Эксель пропустить пустые ячейки вставка макрос

Обновлено: 07.07.2024

Как известно, для полноценной работы с данными (фильтрации, сортировки, подведения итогов и т.д.) нужен непрерывный список, т.е. таблица без разрывов (пустых строк и ячеек - по возможности). На практике же часто мы имеем как раз таблицы с пропущенными пустыми ячейками - например после копирования результатов сводных таблиц или выгрузок в 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, которые станут верными помощниками в выполнении наиболее частых задач.

Понятие макроса

Термин «Макрос» слышало множество людей. Нередко при запуске таблицы появляется предупреждение: “Этот документ использует макросы, способные навредить данному компьютеру, поэтому они отключены с целью защиты от вредоносных действий”.

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

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

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

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

Макросы можно запускать по нажатию определенной комбинации клавиш. Например, если нажать Ctrl+J, можно запустить подпрограмму.

Интересный факт: известная программа бухгалтерского учета 1C изначально очень напоминала Excel, но потом ее функционал расширился до текущего.

Если же нужно давать компьютеру сложные инструкции, можно воспользоваться редактором Visual Basic, примеры кода в котором мы и рассмотрим немного позже.

Когда какой тип записи макросов использовать?

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

Пример использования макросов №1

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

1. Объявление переменных.
2. Указание ссылок на ячейки Excel.
3. Применение цикла типа For.
4. Применение условного оператора.
5. Отображение оповещения.

Sub Find_String(sFindText As String)

iRowNumber = 0

For i = 1 To 100

If Cells(i, 1).Value = sFindText Then

iRowNumber = i

If iRowNumber = 0 Then

Пример 2

Эта процедура перечисляет все значения числовой последовательности Фибоначчи, вплоть до 1000. В этом примере приводятся следующие возможности макросов Excel:

1. Объявление переменных.
2. Цикл Do While.
3. Ссылки на ячейки текущего листа Excel.
4. Условный оператор.

Sub Fibonacci()

iFib_Next = 0

Do While iFib_Next < 1000

If i = 1 Then

iStep = iFib

iFib = iFib_Next

Cells(i, 1).Value = iFib

iFib_Next = iFib + iStep

Пример 3

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

1. Как объявлять переменные.
2. Работу динамического массива.
3. Цикл Do Until.
4. Ссылки на ячейки в текущем листе Excel.
5. Встроенную функцию Ubound, которая предназначена для определения размера массива.

Sub GetCellValues()

ReDim dCellValues(1 To 10)

Do Until IsEmpty(Cells(iRow, 1))

If UBound(dCellValues) < iRow Then

ReDim Preserve dCellValues(1 To iRow + 9)

dCellValues(iRow) = Cells(iRow, 1).Value

iRow = iRow + 1

Пример 4

Следующая процедура «Sub» читает содержимое ячеек из колонки А другого листа, имеющего название «Sheet2» и с этими значениями осуществляет арифметические операции. Результат вычислений пишется в колонке А текущего листа.

Этот пример показывает:

1. Как объявлять переменные.
2. Объекты Excel.
3. Цикл Do Until.
4. Доступ к листам электронных таблиц и диапазонам ячеек с текущей книги.

Sub Transfer_ColA()

Dim i As Integer

Dim Col As Range

Dim dVal As Double

Do Until IsEmpty(Col.Cells(i))

Cells(i, 1) = dVal

Пример 5

Этот пример макроса приводит пример кода VBA, связанного с событием. Каждый раз, когда человек выделяет ячейку или диапазон значений, связанное с макросом событие активируется.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Count = 1 And Target.Row = 1 And Target.Column = 2 Then

Пример 6

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

Sub Set_Values(Val1 As Double, Val2 As Double)

Dim DataWorkbook As Workbook

On Error GoTo ErrorHandling

DataWorkbook.Close

ErrorHandling:

После детального ознакомления с этими примерами будет значительно легче применять свои навыки на практике.

Рекомендации по использованию макросов

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

1. Перед тем, как записывать макрос с помощью рекордера, следует заранее продумать все свои действия, поскольку автоматизироваться будут все действия (в том числе, и ошибочные).
2. Не стоит торопиться, поскольку паузы при записи макросов не учитываются. Вполне можно начать продумывать по ходу какие-то действия. А все записанные операции будут обработаны в один момент.
3. Обязательно необходимо научиться использовать режим отладки макроса. Если возникают какие-то ошибки, он поможет обнаружить, в чем причина неполадки. На первых порах без ошибок не обойтись, потому что в реальной программе будет все не так идеально, как может показаться на первый взгляд.
4. Перед использованием макросов, сделанных другими людьми, нужно настроить антивирусную программу на их обнаружение. Как правило, эта опция установлена по умолчанию.
5. Если загружаются документы из сомнительных источников, следует выбрать опцию «Отключить макросы» при их открытии. И не рекомендуется менять настройки, которые выставлены по умолчанию в настройках безопасности Excel.

Выводы

Таким образом, макросы – это эффективный инструмент автоматизации рабочих процессов в Excel. Он позволяет автоматизировать даже самые сложные последовательности действий. Если необходимо сделать простую программку, то достаточно воспользоваться встроенной функцией для записи макросов. Для более сложных необходимо освоить язык VBA, который является простым для изучения и очень гибким.

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

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

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

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

1. В первую очередь нужно отметить все ячейки рабочей таблицы. Для этого можно использовать только мышку или добавить клавиши SHIFT, CTRL для выделения.
2. После этого нажать комбинацию клавиш на клавиатуре CTRL+G (еще один способ – F5).
3. На экране должно появиться небольшое окошко под названием Go To.
4. Нажать на кнопку “Выделить”.

Для того чтобы отмечать клетки в таблице, на основной панели с инструментами необходимо найти функцию “Найти и выделить”. После этого появится контекстное меню, из которого нужно выбрать выделение определенных значений – формулы, ячейки, константы, примечания, свободные клетки. Выбрать функцию “Выделить группу ячеек. Далее откроется окно настройки, в котором необходимо поставить галочку напротив параметра “Пустые ячейки”. Чтобы сохранить настройки, нужно нажать кнопку “ОК”.

Способ заполнения пустых ячеек вручную

Самый простой способ наполнения пустых клеток рабочей таблицы значениями из верхних ячеек – через функцию “Заполнить пустые ячейки”, которая находится на панели XLTools. Порядок действий:

1. Нажать на кнопку активация функции “Заполнить пустые ячейки”.
2. Должно открыться окно с настройками. После этого необходимо отметить диапазон ячеек, среди которых необходимо заполнить пустые места.
3. Определиться со способом заполнения – из доступных вариантов нужно выбрать: влево, вправо, вверх, вниз.
4. Поставить флажок напротив пункта “Отменить объединение ячеек”.

Останется нажать кнопку “ОК” чтобы пустые клетки заполнились требуемой информацией.

Важно! Одна из полезных особенностей данной функции – сохранение выставленных значений. Благодаря этому можно будет повторить действие со следующим диапазоном ячеек без перенастройки функции.

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

Существует несколько вариантов заполнения пустых клеток в рабочей таблице Excel:

1. Заполнение влево. После активирования данной функции, пустые ячейки будут заполнены данными из клеток справа.
2. Заполнение вправо. После нажатия на данного значение пустые клетки будут заполнены информацией из клеток слева.
3. Заполнение вверх. Ячейки, расположенные сверху, будут заполнены данными из клеток, которые находится снизу.
4. Заполнение вниз. Наиболее популярный вариант заполнения пустых клеток. Информация из ячеек сверху переносится в клетки таблицы, расположенные внизу.

Функция “Заполнить пустые ячейки” точно копирует те значения (числовые, буквенные), которые расположены в заполненных клетках. При этом здесь есть некоторые особенности:

1. Даже при сокрытии или блокировке заполненной клетки, информация из нее будет перенесена в свободную ячейку после активации данной функции.
2. Очень часто случаются ситуации, что значением для переноса является функция, формула, ссылка на другие клетки в рабочей таблице. В этом случае пустая клетка будет заполнена выбранным значением без его изменения.

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

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

Более простой и быстрый способ заполнения ячеек в таблице данных из соседних клеток – через использование специальной формулы. Порядок действий:

1. Отметить все пустые ячейки способом, который был описан выше.
2. Выбрать строку для ввода формул ЛКМ или нажать на кнопку F
3. Ввести символ “=”.

1. После этого указать ячейку, расположенную выше. Формула должна обозначить ту клетку, из которой будет скопирована информация в свободную ячейку.

Последним действием нажать комбинацию клавиш “CTRL+Enter”, чтобы формула сработала для всех свободных клеток.

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

Заполнение пустых ячеек с помощью макроса

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

Для добавления макроса нужно выполнить несколько действий:

1. Нажать комбинацию клавиш ALT+F
2. После этого откроется редактор VBA. В свободное окно нужно вставить представленный выше код.

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

Заключение

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

Имеем диапазон ячеек с данными, в котором есть пустые ячейки:

Задача - удалить пустые ячейки, оставив только ячейки с информацией.

Способ 1. Грубо и быстро

1. Выделяем исходный диапазон
2. Жмем клавишу F5, далее кнопка Выделить(Special) . В открывшмся окне выбираем Пустые ячейки(Blanks) и жмем ОК.

Способ 2. Формула массива

Для упрощения дадим нашим рабочим диапазонам имена, используя Диспетчер Имен (Name Manager) на вкладке Формулы (Formulas) или - в Excel 2003 и старше - меню Вставка - Имя - Присвоить (Insert - Name - Define)

Диапазону B3:B10 даем имя ЕстьПустые, диапазону D3:D10 - НетПустых. Диапазоны должны быть строго одного размера, а расположены могут быть где угодно относительно друг друга.

Теперь выделим первую ячейку второго диапазона (D3) и введем в нее такую страшноватую формулу:

В английской версии это будет:
=IF(ROW()-ROW(НетПустых)+1>ROWS(ЕстьПустые)-COUNTBLANK(ЕстьПустые),"",INDIRECT(ADDRESS(SMALL((IF(ЕстьПустые<>"",ROW(ЕстьПустые),ROW()+ROWS(ЕстьПустые))),ROW()-ROW(НетПустых)+1),COLUMN(ЕстьПустые),4)))

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

Способ 3. Пользовательская функция на VBA

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

Для этого откройте редактор Visual Basic (ALT+F11), вставьте новый пустой модуль (меню Insert - Module) и скопируйте туда текст этой функции:

Не забудьте сохранить файл и вернитесь из редактора Visual Basic в Excel. Чтобы использовать эту функцию в нашем примере:

Читайте также:

  
• 1с неверный тип параметра допустимы только примитивные типы ссылки структура
  
• Как в 1с ерп установить рабочую дату для автоматической подстановки в документы
  
• Криптекс программа для майнинга на андроид
  
• Как посмотреть адрес электронной почты в outlook
  
• Для чего в документах word используется элемент надпись