Копируются пустые ячейки в эксель
Как известно, для полноценной работы с данными (фильтрации, сортировки, подведения итогов и т.д.) нужен непрерывный список, т.е. таблица без разрывов (пустых строк и ячеек - по возможности). На практике же часто мы имеем как раз таблицы с пропущенными пустыми ячейками - например после копирования результатов сводных таблиц или выгрузок в 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) .
Добрый день.
Помогите разобраться плз.
Ниже кусок макроса, в рамках которого у меня перемещаются данные из файла All_data (во вложении) во второй файл Excel.
При этом (смотрим вложенный файл), если в перемещаемой ячейке листа "All" копируется строка 2 (есть текст), то проблем не возникает.
А вот если копируются все остальные строки (например 3 и далее), в которых нет текста, но содержатся формулы, то во втором файле создается пустая строка, чего мне важно избежать! Формулами в Excel я умею это делать, используя ДЛСТР(A1)>0, а вот в VBA не силен.
Ситуация в том, что количество строк в закладках "data" постоянно меняется и поэтому приходится формулами прятать нули и Н/Д на закладке Алл, т.к. она служит отчетом.
Вот кусок макроса из второго файла. Как сделать так, чтобы он не тянул данные, где нет текста и есть формулы?
[vba]
' будем брать данные с первого листа
Set sh = WB.Worksheets(1)
' берем диапазон ячеек с ячейки A2 до последней заполненной в столбце A
Set ra = sh.Range(sh.Range("a2"), sh.Range("a" & sh.Rows.Count).End(xlUp)).Resize(, 14)
' ==== переносим данные в наш файл (shd - кодовое имя листа, куда помещаем данные)
shd.Range("b" & shd.Rows.Count).End(xlUp).Offset(1).Resize(ra.Rows.Count, ra.Columns.Count).Value = ra.Value
' ==== конец обработки данных из очередного файла
Добрый день.
Помогите разобраться плз.
Ниже кусок макроса, в рамках которого у меня перемещаются данные из файла All_data (во вложении) во второй файл Excel.
При этом (смотрим вложенный файл), если в перемещаемой ячейке листа "All" копируется строка 2 (есть текст), то проблем не возникает.
А вот если копируются все остальные строки (например 3 и далее), в которых нет текста, но содержатся формулы, то во втором файле создается пустая строка, чего мне важно избежать! Формулами в Excel я умею это делать, используя ДЛСТР(A1)>0, а вот в VBA не силен.
Ситуация в том, что количество строк в закладках "data" постоянно меняется и поэтому приходится формулами прятать нули и Н/Д на закладке Алл, т.к. она служит отчетом.
Вот кусок макроса из второго файла. Как сделать так, чтобы он не тянул данные, где нет текста и есть формулы?
[vba]
' будем брать данные с первого листа
Set sh = WB.Worksheets(1)
' берем диапазон ячеек с ячейки A2 до последней заполненной в столбце A
Set ra = sh.Range(sh.Range("a2"), sh.Range("a" & sh.Rows.Count).End(xlUp)).Resize(, 14)
' ==== переносим данные в наш файл (shd - кодовое имя листа, куда помещаем данные)
shd.Range("b" & shd.Rows.Count).End(xlUp).Offset(1).Resize(ra.Rows.Count, ra.Columns.Count).Value = ra.Value
' ==== конец обработки данных из очередного файла
Вот кусок макроса из второго файла. Как сделать так, чтобы он не тянул данные, где нет текста и есть формулы?
[vba]
' будем брать данные с первого листа
Set sh = WB.Worksheets(1)
' берем диапазон ячеек с ячейки A2 до последней заполненной в столбце A
Set ra = sh.Range(sh.Range("a2"), sh.Range("a" & sh.Rows.Count).End(xlUp)).Resize(, 14)
' ==== переносим данные в наш файл (shd - кодовое имя листа, куда помещаем данные)
shd.Range("b" & shd.Rows.Count).End(xlUp).Offset(1).Resize(ra.Rows.Count, ra.Columns.Count).Value = ra.Value
' ==== конец обработки данных из очередного файла
n = Application.WorksheetFunction.Match("", Columns("a"), 0)
' берем диапазон ячеек с ячейки A2 до последней заполненной в столбце A
Set ra = sh.Range(sh.Range("a2"), sh.Range("a" & n)).Resize(, 14)
n = Application.WorksheetFunction.Match("", Columns("a"), 0)
' берем диапазон ячеек с ячейки A2 до последней заполненной в столбце A
Set ra = sh.Range(sh.Range("a2"), sh.Range("a" & n)).Resize(, 14)
n = Application.WorksheetFunction.Match("", Columns("a"), 0)
' берем диапазон ячеек с ячейки A2 до последней заполненной в столбце A
Set ra = sh.Range(sh.Range("a2"), sh.Range("a" & n)).Resize(, 14)
Добрый день,
спасибо за ваши предложения, но оба предложенных способов не работают должным образом:
SLAVICK,
результатом кода стало вставка только одной пустой строки между данными с разных отчетов (у меня ранее вставлялись все имющиеся пустые строки с формулами).
_Boroda_,
результат вообще отсутствует, во второй файл ничего не переносится.
Добрый день,
спасибо за ваши предложения, но оба предложенных способов не работают должным образом:
SLAVICK,
результатом кода стало вставка только одной пустой строки между данными с разных отчетов (у меня ранее вставлялись все имющиеся пустые строки с формулами).
_Boroda_,
результат вообще отсутствует, во второй файл ничего не переносится. Callione
SLAVICK,
результатом кода стало вставка только одной пустой строки между данными с разных отчетов (у меня ранее вставлялись все имющиеся пустые строки с формулами).
_Boroda_,
результат вообще отсутствует, во второй файл ничего не переносится. Автор - Callione
Дата добавления - 21.03.2016 в 10:47
оно работает)
понимаю что костыль, но сейчас главное результат! Спасибо! Автор - Callione
Дата добавления - 21.03.2016 в 12:20
Что бы было без костылей, надо было на 2-х листах сделать разные отчеты как есть, а на третьем показать как надо. И результат мог быть без костылей. ИМХО.
Что бы было без костылей, надо было на 2-х листах сделать разные отчеты как есть, а на третьем показать как надо. И результат мог быть без костылей. ИМХО. Wasilich
Что бы было без костылей, надо было на 2-х листах сделать разные отчеты как есть, а на третьем показать как надо. И результат мог быть без костылей. ИМХО. Автор - Wasilich
Дата добавления - 21.03.2016 в 14:15
SLAVICK, обнаружил, что предложенное вами решение не работает при обработке отчета в 37 тыс строк, оно просто игнорирует этот отчет..
Если отключаю ваши две строчки и включаю старую - все работает ок и этот проблемный отчет (в т.ч.) тоже копируется во второй файл.
Не знаете, почему такое происходит?
В целях проверки в проблемном отчете удалил все записи, кроме первых пары десятков строк. Ваш вариант макроса нормально обрабатывает этот отчет. Как только копирую в этот файл обратно все 37 тыс строк.. отчет игнорируется.
Wasilich, видимо так и произойдет, если на пальцах (в теории) не найдем понимания. Структура отчетов очень сложная, чтобы подготовить рабочий шаблон для размещения тут в примерах, уйдет минут 40, нет сейчас столько времени, извините.
[moder]Ну да, правильно, зачем Вам свое время тратить, если можно тратить наше - вдруг кто-нибудь да угадает?
SLAVICK, обнаружил, что предложенное вами решение не работает при обработке отчета в 37 тыс строк, оно просто игнорирует этот отчет..
Если отключаю ваши две строчки и включаю старую - все работает ок и этот проблемный отчет (в т.ч.) тоже копируется во второй файл.
Не знаете, почему такое происходит?
В целях проверки в проблемном отчете удалил все записи, кроме первых пары десятков строк. Ваш вариант макроса нормально обрабатывает этот отчет. Как только копирую в этот файл обратно все 37 тыс строк.. отчет игнорируется.
Wasilich, видимо так и произойдет, если на пальцах (в теории) не найдем понимания. Структура отчетов очень сложная, чтобы подготовить рабочий шаблон для размещения тут в примерах, уйдет минут 40, нет сейчас столько времени, извините.
[moder]Ну да, правильно, зачем Вам свое время тратить, если можно тратить наше - вдруг кто-нибудь да угадает? Callione
В целях проверки в проблемном отчете удалил все записи, кроме первых пары десятков строк. Ваш вариант макроса нормально обрабатывает этот отчет. Как только копирую в этот файл обратно все 37 тыс строк.. отчет игнорируется.
Wasilich, видимо так и произойдет, если на пальцах (в теории) не найдем понимания. Структура отчетов очень сложная, чтобы подготовить рабочий шаблон для размещения тут в примерах, уйдет минут 40, нет сейчас столько времени, извините.
[moder]Ну да, правильно, зачем Вам свое время тратить, если можно тратить наше - вдруг кто-нибудь да угадает? Автор - Callione
Дата добавления - 21.03.2016 в 15:25
Саш тут и угадывать не нужно.На лицо лимит WorksheetFunction
Callione, - Wasilich прав - нужно сразу готовить правильные примеры (или говорить об объемах)- откуда мы знаем сколько и каких у Вас там данных?
Вот можно так:
[vba]
[/vba]
Или вообще через анализ массива - так больше код, но легче и удобнее корректировать. Можно быстро менять условия.
[vba]
Function lastRow(r As Range)
Dim m()
m = r.Value
For n = UBound(m) To 1 Step -1
If Len(m(n, 1)) > 0 Then Exit For
Next
lastRow = n
End Function
Похоже не только в этом проблема на файле из 1- го поста работает только 3-й вариант. , хотя мой пример на данных того листа.
Добавлено
Понял в чем проблема 1-2-го варианта на примере файла не было формулы возвращающей "".
Используйте 3-й вариант он надежней - хоть и больше.
Саш тут и угадывать не нужно.На лицо лимит WorksheetFunction
Callione, - Wasilich прав - нужно сразу готовить правильные примеры (или говорить об объемах)- откуда мы знаем сколько и каких у Вас там данных?
Вот можно так:
[vba]
[/vba]
Или вообще через анализ массива - так больше код, но легче и удобнее корректировать. Можно быстро менять условия.
[vba]
Function lastRow(r As Range)
Dim m()
m = r.Value
For n = UBound(m) To 1 Step -1
If Len(m(n, 1)) > 0 Then Exit For
Next
lastRow = n
End Function
Похоже не только в этом проблема на файле из 1- го поста работает только 3-й вариант. , хотя мой пример на данных того листа.
Добавлено
Понял в чем проблема 1-2-го варианта на примере файла не было формулы возвращающей "".
Используйте 3-й вариант он надежней - хоть и больше. SLAVICK
Иногда все проще чем кажется с первого взгляда.
Callione, - Wasilich прав - нужно сразу готовить правильные примеры (или говорить об объемах)- откуда мы знаем сколько и каких у Вас там данных?
Вот можно так:
[vba]
[/vba]
Или вообще через анализ массива - так больше код, но легче и удобнее корректировать. Можно быстро менять условия.
[vba]
Function lastRow(r As Range)
Dim m()
m = r.Value
For n = UBound(m) To 1 Step -1
If Len(m(n, 1)) > 0 Then Exit For
Next
lastRow = n
End Function
Похоже не только в этом проблема на файле из 1- го поста работает только 3-й вариант. , хотя мой пример на данных того листа.
Добавлено
Понял в чем проблема 1-2-го варианта на примере файла не было формулы возвращающей "".
Используйте 3-й вариант он надежней - хоть и больше. Автор - SLAVICK
Дата добавления - 21.03.2016 в 16:37
SLAVICK, Wasilich, Вы безусловно правы, прошу меня извинить. Вот уж точно век живи и век учись - я даже и представить не мог, что у функции может существовать подобный лимит.
SLAVICK, подставил третий вариант, даже ничего подгонять не пришлось - все заработало с первого раза! В том числе и тот самый отчет :)
Спасибо, что нашли возможность помочь!
SLAVICK, Wasilich, Вы безусловно правы, прошу меня извинить. Вот уж точно век живи и век учись - я даже и представить не мог, что у функции может существовать подобный лимит.
SLAVICK, подставил третий вариант, даже ничего подгонять не пришлось - все заработало с первого раза! В том числе и тот самый отчет :)
Спасибо, что нашли возможность помочь! Callione
SLAVICK, подставил третий вариант, даже ничего подгонять не пришлось - все заработало с первого раза! В том числе и тот самый отчет :)
Спасибо, что нашли возможность помочь! Автор - Callione
Дата добавления - 21.03.2016 в 19:40
Проблема в том, что при копировании диапазона ячеек с одного файла в другой файл вставляются пустые ячейки.
Проблема в том, что при копировании диапазона ячеек с одного файла в другой файл вставляются пустые ячейки. alexsasha
Вероятно в исходном файле они были скрыты или может это макрос какой хулиганит. Не видя исходного файла больше предположить нечего
Вероятно в исходном файле они были скрыты или может это макрос какой хулиганит. Не видя исходного файла больше предположить нечего Serge_007
файл весит 2 Mb поэтому отправляю скрины ошибок
Мне надо с одного файла в другой копировать информацию. не могу сказать в какой момент, но через какое-то время вставка завершается пустотой, т.е. не копируется, файл получается уже битым. При повторном открытии файла выходят след ошибки, см файл.
файл весит 2 Mb поэтому отправляю скрины ошибок
Мне надо с одного файла в другой копировать информацию. не могу сказать в какой момент, но через какое-то время вставка завершается пустотой, т.е. не копируется, файл получается уже битым. При повторном открытии файла выходят след ошибки, см файл. alexsasha
alexsasha, вот скажите, зачем на форуме по Excel выкладывать файлы Worda?! Нет его у меня, не пользуюсь я такой программой. На работе установлен корпоративно, а дома нет в принципе. Что мешало те же скрины вложить в файл Excel? Или выложить сами скрины сюда, минуя посредников в виде сторонних программ?
alexsasha, вот скажите, зачем на форуме по Excel выкладывать файлы Worda?! Нет его у меня, не пользуюсь я такой программой. На работе установлен корпоративно, а дома нет в принципе. Что мешало те же скрины вложить в файл Excel? Или выложить сами скрины сюда, минуя посредников в виде сторонних программ? Serge_007
Serge_007, извините пожалуйста, у меня никаких мыслей не было . просто я привыкла наверное так делать.
Повторно высылаю скрины в xls
Serge_007, извините пожалуйста, у меня никаких мыслей не было . просто я привыкла наверное так делать.
Повторно высылаю скрины в xls alexsasha
По приложенным скринам причину определить вряд ли получится.
Могу предположить что Вы копируете очень большие диапазоны, в которых есть много форматирования, после чего во втором файле происходит сбой. Попробуйте копировать из вновь созданной книги в конечную и наоборот из исходной в новую. Что будет происходить? Будет появляться ошибка? Если да - то в каком из случаев? Естественно делайте это всё предварительно сохранив копии исходных файлов
По приложенным скринам причину определить вряд ли получится.
Могу предположить что Вы копируете очень большие диапазоны, в которых есть много форматирования, после чего во втором файле происходит сбой. Попробуйте копировать из вновь созданной книги в конечную и наоборот из исходной в новую. Что будет происходить? Будет появляться ошибка? Если да - то в каком из случаев? Естественно делайте это всё предварительно сохранив копии исходных файлов
диапазон с размером 3 на 150 - копирую например 4 раза подряд (естественно содержимое разное) - это считается большим диапазоном?
Я пробовала эти диапазоны копировать в пустой файл - все нормально.
Кажется файле куда я копирую (он огромен. с кучей формул и т.д.) что-то неправильно срабатывает.
Я Вам высылаю еще содержимое ошибки, подскажите пожалуйста, по нему можно определить, в каком месте у меня "зараза"сидит.
Заранее благодарна Вам.
диапазон с размером 3 на 150 - копирую например 4 раза подряд (естественно содержимое разное) - это считается большим диапазоном?
Я пробовала эти диапазоны копировать в пустой файл - все нормально.
Кажется файле куда я копирую (он огромен. с кучей формул и т.д.) что-то неправильно срабатывает.
Я Вам высылаю еще содержимое ошибки, подскажите пожалуйста, по нему можно определить, в каком месте у меня "зараза"сидит.
Заранее благодарна Вам. alexsasha
Заранее благодарна Вам. Автор - alexsasha
Дата добавления - 20.06.2013 в 10:24
Вы на скрин сами-то смотрели?
Там адрес файла проблемного есть и даже что удалено написано. Удалённо сказать в чём проблема не могу и устранить её тоже. Обращайтесь к своим IT-специалистам
Вы на скрин сами-то смотрели?
Там адрес файла проблемного есть и даже что удалено написано. Удалённо сказать в чём проблема не могу и устранить её тоже. Обращайтесь к своим IT-специалистам Serge_007
Вы на скрин сами-то смотрели?
Там адрес файла проблемного есть и даже что удалено написано. Удалённо сказать в чём проблема не могу и устранить её тоже. Обращайтесь к своим IT-специалистам Автор - Serge_007
Дата добавления - 20.06.2013 в 10:36
Добрый день, столкнулся с такой же проблемой, поиском по интернетам наткнулся на ваш форум. В приложении файл, в который копируются пустые ячейки из другой книги. Заполняется ежедневно, до сегодняшнего дня все было ок, в прошлые месяца с анологичным файлом тоже все было ок(вру, 1 раз возникла такая же ошибка, решилась пересозданием). На корявость формул просьба не обращать внимания, я не гуру, только начинаю пользоваться сей программой. Кстать, я заметил, что если после копирования пустых ячеек, файл сохранить, то открыв его второй раз увидим, что все форматы ячеек(цвета, шрифты) будут не такими, как в оригинале.
Если сохранить в xlsx, то проблема исчезает, но мне, к сожалению, нужен xls, так как этот файл читают люди с опенофисом, и он у них некорректно отображается иначе.
Заранее спасибо за любой ответ по проблеме.
зы: прошу прошения, что в архиве - файл весит больше, чем максимально разрешено на форуме
Добрый день, столкнулся с такой же проблемой, поиском по интернетам наткнулся на ваш форум. В приложении файл, в который копируются пустые ячейки из другой книги. Заполняется ежедневно, до сегодняшнего дня все было ок, в прошлые месяца с анологичным файлом тоже все было ок(вру, 1 раз возникла такая же ошибка, решилась пересозданием). На корявость формул просьба не обращать внимания, я не гуру, только начинаю пользоваться сей программой. Кстать, я заметил, что если после копирования пустых ячеек, файл сохранить, то открыв его второй раз увидим, что все форматы ячеек(цвета, шрифты) будут не такими, как в оригинале.
Если сохранить в xlsx, то проблема исчезает, но мне, к сожалению, нужен xls, так как этот файл читают люди с опенофисом, и он у них некорректно отображается иначе.
Заранее спасибо за любой ответ по проблеме.
зы: прошу прошения, что в архиве - файл весит больше, чем максимально разрешено на форуме Debillo
Когда вы вставляете данные, которые вы скопировали в Microsoft Excel, содержимое ячеек назначения автоматически перезаписывается. Это может быть целью во многих случаях, но, вероятно, нет, если скопированные ячейки содержат пробелы.
С помощью пары дополнительных щелчков мышью вы можете пропустить пробелы при вставке в Microsoft Excel. Вставляя все, кроме пустых ячеек, вы можете сохранить данные, которые уже есть, и просто заменить остальные.
Как пропустить пустые ячейки при вставке в Excel
Чтобы лучше всего проиллюстрировать, как пропускать пробелы при вставке в Excel, мы воспользуемся примером. В левой части нашего листа указаны предыдущие суммы продаж, а справа — обновленные суммы. Нам нужно заменить предыдущее на обновленное.
Но, как вы можете видеть, обновленные суммы включают пробелы, потому что эти конкретные продажи не изменились. Итак, мы хотим оставить существующие суммы в этих случаях как есть. Мы выделили конкретный пример на скриншоте ниже.
Если мы скопируем и вставим обновленный диапазон ячеек, пробелы перезапишут наши существующие суммы. Конечно, мы можем копировать и вставлять по одной ячейке за раз или даже сразу несколько соседних ячеек. Но, чтобы избавиться от этой лишней работы, мы можем скопировать и вставить весь диапазон и заменить все Кроме заготовки.
Выделите ячейки, которые хотите скопировать, и щелкните правой кнопкой мыши и выберите «Копировать», либо перейдите на вкладку «Главная» и нажмите «Копировать» в разделе «Буфер обмена» на ленте.
Выберите целевые ячейки, в которые вы хотите вставить скопированные ячейки. Либо щелкните правой кнопкой мыши, выберите «Специальная вставка» и выберите «Специальная вставка» во всплывающем меню, либо перейдите на вкладку «Главная», нажмите «Вставить» на ленте и выберите «Специальная вставка».
Когда откроется окно «Специальная вставка», установите внизу флажок «Пропустить пробелы». При необходимости вы можете настроить любые другие параметры в разделах «Вставить» и «Операция». По завершении нажмите «ОК».
После этого вы должны увидеть, что ваши скопированные ячейки вставлены в целевые ячейки, при этом существующие данные не будут перезаписаны пробелами.
Параметр «Специальная вставка для пропуска пробелов» работает не только с диапазонами ячеек. Вы также можете использовать его, если вставляете столбцы или строки, содержащие пустые ячейки.
Если вы работаете с электронной таблицей, в которой есть скрытые ячейки, узнайте, как копировать и вставлять только видимые ячейки в Excel.
Как скопировать и вставить только непустые ячейки в Excel?
Большинство из нас может столкнуться с этой проблемой, когда мы копируем список данных, который содержит несколько пустых ячеек, а затем вставляем их в другой столбец, пустые ячейки также будут вставлены. Это очень раздражает, когда нам не нужны пробелы и мы хотим вставить только непустые ячейки. Как в таком случае быстро и удобно копировать и вставлять в Excel только непустые ячейки? Вот несколько быстрых приемов, чтобы решить эту проблему:
Скопируйте и вставьте только непустые ячейки с помощью Kutools for Excel
Скопируйте и вставьте только непустые ячейки с помощью команды Перейти к специальной
Для Перейти к специальному , вы можете сначала выбрать все данные, а затем скопировать и вставить их в другое место.
1. Выберите свой список данных, которые вы хотите использовать.
2. Затем нажмите Главная > Найти и выбрать > Перейти к специальному, см. снимок экрана:
3. В Перейти к специальному диалоговое окно, отметьте Константы вариант, см. снимок экрана:
4. Затем нажмите OK, и в списке выбраны только ячейки значений.
5. А затем скопируйте и вставьте данные в нужное место. И были вставлены только непустые значения ячеек. Смотрите скриншот:
Внимание: Этот метод доступен только для констант, он не применяется к ячейкам формулы.
Копируйте и вставляйте только непустые ячейки с помощью функции фильтра
Фильтр Функция может помочь вам сначала отфильтровать непустые ячейки, а затем вы можете скопировать и вставить их в другие ячейки по мере необходимости.
2. Нажмите Данные > Фильтр, см. снимок экрана:
3. Затем нажмите кнопку раскрывающегося списка в правом углу ячейки в выбранном списке и снимите флажок Пробелы вариант в раскрывающемся меню. Смотрите скриншот:
4, Затем нажмите OK, все непустые ячейки были отфильтрованы, как показано на следующем снимке экрана:
5. Наконец, вы можете скопировать отфильтрованные данные и вставить их в любое нужное место.
Внимание: Таким образом вступают в силу и значения, и формулы.
Выбрать непустые ячейки сразу
Скопируйте и вставьте только непустые ячейки с формулой массива
Чтобы скопировать и вставить только непустые ячейки, вы также можете применить следующую формулу массива.
1. Помимо ваших данных, введите в пустую ячейку следующую формулу:
=LOOKUP("zzzzz",CHOOSE(,"",INDEX(A:A,SMALL(IF($A$1:$A$15<>"",ROW($A$1:$A$15)),ROWS($B$1:B1)))))
Внимание: В приведенной выше формуле A1: A15 - это список данных, который вы хотите использовать. Вы можете изменить его по своему усмотрению.
2, Затем нажмите Shift + Ctrl + Enter вместе, а затем выберите ячейку B1 и перетащите маркер заполнения в диапазон, который вы хотите содержать эту формулу, и все непустые значения ячеек были извлечены. Смотрите скриншот:
3. Поскольку они являются формулами, когда вы копируете их в другое место, вы должны копировать и вставлять их как значения.
Внимание: Эта формула доступна только для констант и не применяется к ячейкам формулы.
Скопируйте и вставьте только непустые ячейки с кодом VBA
Если вас интересует код VBA, вы можете выполнить следующий код, чтобы завершить эту задачу.
1. Удерживайте ALT + F11 ключи, и он открывает Окно Microsoft Visual Basic для приложений.
2. Нажмите Вставить > Модулии вставьте следующий код в Окно модуля.
Код VBA: копируйте и вставляйте только непустые ячейки в Excel
3, Затем нажмите F5 Нажмите клавишу для запуска этого кода, появится окно с подсказкой, напоминающее вам о выборе диапазона данных, который вы хотите использовать. Смотрите скриншот:
4, Затем нажмите OKпоявится другое окно подсказки, в котором вы сможете выбрать ячейку для ввода данных.
5. Нажмите OK чтобы завершить этот код, и только непустые значения ячеек были вставлены в указанную вами позицию.
Внимание: Этот код доступен только для констант.
Скопируйте и вставьте только непустые ячейки с помощью Kutools for Excel
Есть ли способ намного проще, чем указано выше? Конечно, Kutools for Excel's Выбрать непустую ячейкуУтилита s может помочь вам сначала выбрать непустые ячейки, а затем скопировать и вставить их.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Выберите диапазон ячеек и щелкните Кутулс > Выберите > Выбрать непустые ячейки. Смотрите скриншот:
2. Затем выбираются непустые ячейки, нажимаем Ctrl + C чтобы скопировать их, затем выберите ячейку, в которой вы хотите вывести результат, нажмите Ctrl + V для вставки выбранных непустых ячеек. Смотрите скриншот:
Читайте также: