Как скопировать только заполненные ячейки в excel
Как известно, для полноценной работы с данными (фильтрации, сортировки, подведения итогов и т.д.) нужен непрерывный список, т.е. таблица без разрывов (пустых строк и ячеек - по возможности). На практике же часто мы имеем как раз таблицы с пропущенными пустыми ячейками - например после копирования результатов сводных таблиц или выгрузок в 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) .
как в экселе скопировать данные без скрытых ячеек
нашла вот это - Выделяете диапазон -- F5 -- Выделить -- Только видимые ячейки -- Ок. А дальше уже копируете, как обычно - но где все это найти на клавиатуре не могу.
еще вот это нашла
Меню
Правка
Перейти
Выделить
Только виденные ячейки
Копировать
Вставить
но не могу все это найти в компе.
Понимаю, что туплю, но надо сделать.
как в экселе скопировать данные без скрытых ячеек
нашла вот это - Выделяете диапазон -- F5 -- Выделить -- Только видимые ячейки -- Ок. А дальше уже копируете, как обычно - но где все это найти на клавиатуре не могу.
еще вот это нашла
Меню
Правка
Перейти
Выделить
Только виденные ячейки
Копировать
Вставить
но не могу все это найти в компе.
Понимаю, что туплю, но надо сделать. solotze
но не могу все это найти в компе.
Понимаю, что туплю, но надо сделать. Автор - solotze
Дата добавления - 16.07.2014 в 13:38
Вы же всё сами написали.
1) Выделяете весь диапазон
2) Тыкаете F5
3) В появившемся окне внизу кнопочка "выделить"
4) Ставите флажок на "Только видимые ячейки"
5) Тыкаете ОК
6) У вас автоматом выделяются только видимые ячейки, вы их ctrl+с и куда нужно ctrl+v
Вы же всё сами написали.
1) Выделяете весь диапазон
2) Тыкаете F5
3) В появившемся окне внизу кнопочка "выделить"
4) Ставите флажок на "Только видимые ячейки"
5) Тыкаете ОК
6) У вас автоматом выделяются только видимые ячейки, вы их ctrl+с и куда нужно ctrl+v ArkaIIIa
1) Выделяете весь диапазон
2) Тыкаете F5
3) В появившемся окне внизу кнопочка "выделить"
4) Ставите флажок на "Только видимые ячейки"
5) Тыкаете ОК
6) У вас автоматом выделяются только видимые ячейки, вы их ctrl+с и куда нужно ctrl+v Автор - ArkaIIIa
Дата добавления - 16.07.2014 в 13:50
Рис.1 - Файл-исходник – Как видно на картинке – Ячейка состоит из 4-х столбцов и 4-х строк . И более того – Всем Ячейкам Присвоены Имена … типа gr2r1str102 – что означает По-Русски – Графа-1, Раздел-1 (Таблица-1), Строка-102
И на Рис.2 – показано Каким должен быть Файл-Готовый для отправки – Обычная Стандартная форма Excel
Обычные способы Копирования и Спец.Вставки нужного результата не дают .
Подскажите какой-нибудь способ / метод – КАК ПЕРЕНЕСТИ ДАННЫЕ из Объединенных таблиц в Обычные Таблицы – ЛЕГКИМ ДОСТУПНЫМ И УДОБНЫМ СПОСОБОМ ! ЗАРАНЕЕ БЛАГОДАРЮ !
Рис.1 - Файл-исходник – Как видно на картинке – Ячейка состоит из 4-х столбцов и 4-х строк . И более того – Всем Ячейкам Присвоены Имена … типа gr2r1str102 – что означает По-Русски – Графа-1, Раздел-1 (Таблица-1), Строка-102
И на Рис.2 – показано Каким должен быть Файл-Готовый для отправки – Обычная Стандартная форма Excel
Обычные способы Копирования и Спец.Вставки нужного результата не дают .
Подскажите какой-нибудь способ / метод – КАК ПЕРЕНЕСТИ ДАННЫЕ из Объединенных таблиц в Обычные Таблицы – ЛЕГКИМ ДОСТУПНЫМ И УДОБНЫМ СПОСОБОМ ! ЗАРАНЕЕ БЛАГОДАРЮ ! vitareiki
Рис.1 - Файл-исходник – Как видно на картинке – Ячейка состоит из 4-х столбцов и 4-х строк . И более того – Всем Ячейкам Присвоены Имена … типа gr2r1str102 – что означает По-Русски – Графа-1, Раздел-1 (Таблица-1), Строка-102
И на Рис.2 – показано Каким должен быть Файл-Готовый для отправки – Обычная Стандартная форма Excel
Обычные способы Копирования и Спец.Вставки нужного результата не дают .
Подскажите какой-нибудь способ / метод – КАК ПЕРЕНЕСТИ ДАННЫЕ из Объединенных таблиц в Обычные Таблицы – ЛЕГКИМ ДОСТУПНЫМ И УДОБНЫМ СПОСОБОМ ! ЗАРАНЕЕ БЛАГОДАРЮ ! Автор - vitareiki
Дата добавления - 12.04.2017 в 18:23
Здравствуйте. Выделите всё--снимите объединение--выделите строку с полностью забитыим данными--F5--выделить. --пустые ячейки--удалить столбцы. Также проделайте для удаления пустых строк.
Здравствуйте. Выделите всё--снимите объединение--выделите строку с полностью забитыим данными--F5--выделить. --пустые ячейки--удалить столбцы. Также проделайте для удаления пустых строк. gling
gling, Спасибо за совет ! - В принципе некоторые таблицы так и делали - Только в ручную удаляли столбцы и строки
Но в вашем способе есть существенный МИНУС - способ можно использовать при всех полностью заполненных ячейках таблицы .
Если в ячейках значений нет - ТО ВМЕСТЕ с Ненужными Пустыми строчками можно удалить и Пустые нужные строки входящие в состав таблицы
Надо бы как-то снять выделение с Нужных строк чтобы Не удалились вместе с Пустыми Ненужными строками
gling, Спасибо за совет ! - В принципе некоторые таблицы так и делали - Только в ручную удаляли столбцы и строки
Но в вашем способе есть существенный МИНУС - способ можно использовать при всех полностью заполненных ячейках таблицы .
Если в ячейках значений нет - ТО ВМЕСТЕ с Ненужными Пустыми строчками можно удалить и Пустые нужные строки входящие в состав таблицы
Надо бы как-то снять выделение с Нужных строк чтобы Не удалились вместе с Пустыми Ненужными строками vitareiki
Как скопировать и вставить только непустые ячейки в 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 для вставки выбранных непустых ячеек. Смотрите скриншот:
Как скопировать только видимые ячейки в Excel?
Когда вы устанавливаете скрытие некоторых строк и столбцов на листе и хотите скопировать только видимые ячейки в Excel, можете ли вы просто скопировать и вставить их напрямую в Excel? Нет, для этого вам следует знать несколько хитростей. Выполните следующие действия, чтобы узнать, как можно копировать только видимые ячейки в Excel.
Скопируйте только видимые строки в Excel с помощью специальной команды Перейти
В Excel вы можете применить Перейти к специальному функция, чтобы сначала выбрать видимые строки, а затем скопировать и вставить их в другое место.
1. Выберите видимые строки, которые вы хотите скопировать.
2. Нажмите Главная > Найти и выбрать > Перейти к специальному, см. снимок экрана:
3. В Перейти к специальному в диалоговом окне выберите Только видимые ячейки вариант под Выберите, см. снимок экрана:
4. Затем нажмите OK кнопку, и будут выделены только видимые ячейки.
5. Затем вы можете скопировать их и вставить в любое место без скрытых строк или столбцов.
Удалить только видимые строки в Excel с помощью Kutools for Excel
Если вам нужно удалить только видимые строки и оставить скрытые строки, вы можете применить описанные выше шаги, чтобы сначала выбрать видимые строки, после выбора видимых строк нажмите клавишу Delete, чтобы удалить их сразу.
Но здесь я также могу представить вам простой и мощный инструмент - Kutools for Excel, С его Удалить скрытые (видимые) строки и столбцы, вы можете быстро удалить скрытые или видимые строки или столбцы только на листе или всей книге.
После установки Kutools for Excel, пожалуйста, сделайте следующее :( Бесплатная загрузка Kutools для Excel сейчас )
1. Выберите видимые строки, которые хотите удалить.
2. Затем нажмите Кутулс > Удалить > Удалить скрытые (видимые) строки и столбцы, см. снимок экрана:
3. В Удалить скрытые (видимые) строки и столбцы диалоговое окно, выберите В выбранном диапазоне из Заглянуть раскрывающийся список, затем выберите Ряды в Удалить тип , и выберите Видимые строки под Подробный тип раздел, см. снимок экрана:
4. Затем нажмите OK Кнопка, все видимые строки были немедленно удалены, но скрытые строки остались.
С помощью этой функции вы также можете удалить пустые строки / столбцы и скрытые строки / столбцы в диапазоне / на листе или в книге по мере необходимости.
Читайте также: