0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как вставить данные в отфильтрованный список, пропуская только скрытые строки в Excel?

Вставить значения в отфильтрованные ячейки. Копирование в Excel отфильтрованных данных. Копирование только видимых ячеек

Вставить только в видимые строки вExcel числа, формулы, текст можно несколькими способами. Когда нужно вставить числа, формулы, текст не во все строки таблицы, можно воспользоваться фильтром. Как установить фильтр и как фильтровать в Excel, смотрите в статье «Фильтр в Excel ». Но, чтобы вставить данные только в видимые ячейки, нужны свои способы, особенно, если много строк.
Первый способ — обычный.
Возьмем такую таблицу. Таблица будет одна для всех примеров.
Уберем фильтром все цифры 2 из таблицы. В оставшиеся видимые ячейки поставим цифру 600. В ячейку В2 ставим число 600, затем копируем его вниз по столбцу (тянем за правый нижний угол ячейки В2). Значения скопировались только в видимые ячейки. Точно также можно вставлять и формулы. Мы в ячейке С2 пишем такую формулу. =А2*10
Получилось так.
Отменим фильтр. Получилась такая таблица.
Формула и цифры вставились только в отфильтрованные строки.
Второй способ.
Также отфильтруем данные. В первой ячейке пишем число, формулу, текст, т.д. Теперь, если строк тысячи, то выделяем ячейки так: нажимаем клавиши «Ctrl» + «Shift» + кнопку (стрелочку) вниз (или кнопку вверх, зависит от того, где хотим выделить ячейки – ниже или выше ячейки, в которой написали число).
Теперь, или нажимаем сочетание клавиш «Ctrl» + G, или клавишу F5. Выйдет диалоговое окно «Переход». Нажимаем кнопку «Выделить…». И, в новом диалоговом окне «Выделение группы ячеек» ставим галочку у слов «Только видимые ячейки». Нажимаем «ОК». Затем вставляем как обычно.

Другой способ — открыть диалоговое окно «Выбрать группу ячеек». Нажмите кнопку «Найти и выбрать» на вкладке «Главная» в разделе «Редактирование». В появившемся списке выберите функцию «Выбрать группу ячеек».

Читайте так же:
Как вернуть номер строки первой или последней непустой ячейки на листе?

Чтобы заполнить видимые ячейки в выбранных столбцах Excel, нажмите Ctrl + D. И все выбранные столбцы будут заполнены данными или формулой, как в первой ячейке. Как видно из нашего примера, число 800 находится в ячейке D2 столбца D.

Третий способ.
В новом столбце (в нашем примере – столбец Е) выделяем ячейки. Нажимаем клавишу F5. Выйдет диалоговое окно «Переход». Нажимаем кнопку «Выделить…». И, в новом диалоговом окне «Выделение группы ячеек», ставим галочку у слов «Только видимые ячейки». Нажимаем «ОК». Теперь, не отменяя выделения, в первой ячейке столбца (у нас – Е2) вводим формулу, цифру, т.д. Нажимаем сочетание клавиш «Ctrl» + «Enter».

Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (39)
Баги и глюки Excel (4)

Как вставить скопированные ячейки только в видимые/отфильтрованные ячейки

В целом, суть статьи ясна уже из заголовка. Я просто хочу немного расширить эту тему.

Excel позволяет пользователям выбирать только видимые столбцы (например, если некоторые были скрыты или был применен фильтр).

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

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

Option Explicit Dim rCopyRange As Range «Этим макросом копируем данные Sub My_Copy() If Selection.Count > 1 Then Set rCopyRange = Selection.SpecialCells(xlVisible) Else : Set rCopyRange = ActiveCell End If End Sub «Этим макросом вставляем данные, начиная с выделенной ячейки Sub My_Paste() If rCopyRange Is Nothing Then Exit Sub If rCopyRange.Areas.Count > 1 Then MsgBox «Вставляемый диапазон не должен содержать более одной области!» , vbCritical, «Неверный диапазон» : Exit Sub Dim rCell As Range, li As Long , le As Long , lCount As Long , iCol As Integer , iCalculation As Integer Application.ScreenUpdating = False iCalculation = Application.Calculation: Application.Calculation = -4135 For iCol = 1 To rCopyRange.Columns.Count li = 0: lCount = 0: le = iCol — 1 For Each rCell In rCopyRange.Columns(iCol).Cells Do If ActiveCell.Offset(li, le).EntireColumn.Hidden = False And _ ActiveCell.Offset(li, le).EntireRow.Hidden = False Then rCell.Copy ActiveCell.Offset(li, le): lCount = lCount + 1 End If li = li + 1 Loop While lCount >= rCell.Row — rCopyRange.Cells(1).Row Next rCell Next iCol Application.ScreenUpdating = True : Application.Calculation = iCalculation End Sub
Читайте так же:
Как в Excel отображать только строки с определенным текстом?

Option Explicit Dim rCopyRange As Range «Этим макросом копируем данные Sub My_Copy() If Selection.Count > 1 Then Set rCopyRange = Selection.SpecialCells(xlVisible) Else: Set rCopyRange = ActiveCell End If End Sub «Этим макросом вставляем данные, начиная с выделенной ячейки Sub My_Paste() If rCopyRange Is Nothing Then Exit Sub If rCopyRange.Areas.Count > 1 Then MsgBox «Вставляемый диапазон не должен содержать более одной области!», vbCritical, «Неверный диапазон»: Exit Sub Dim rCell As Range, li As Long, le As Long, lCount As Long, iCol As Integer, iCalculation As Integer Application.ScreenUpdating = False iCalculation = Application.Calculation: Application.Calculation = -4135 For iCol = 1 To rCopyRange.Columns.Count li = 0: lCount = 0: le = iCol — 1 For Each rCell In rCopyRange.Columns(iCol).Cells Do If ActiveCell.Offset(li, le).EntireColumn.Hidden = False And _ ActiveCell.Offset(li, le).EntireRow.Hidden = False Then rCell.Copy ActiveCell.Offset(li, le): lCount = lCount + 1 End If li = li + 1 Loop While lCount >= rCell.Row — rCopyRange.Cells(1).Row Next rCell Next iCol Application.ScreenUpdating = True: Application.Calculation = iCalculation End Sub

Для полноты картины лучше назначить эти макросы на функциональные клавиши (в приведенных ниже кодах это происходит автоматически при открытии кодовой книги). Для этого просто скопируйте приведенные ниже коды в модуль ThisWorkbook:

Option Explicit «Отменяем назначение горячих клавиш перед закрытием книги Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.OnKey «^q»: Application.OnKey «^w» End Sub «Назначаем горячие клавиши при открытии книги Private Sub Workbook_Open() Application.OnKey «^q», «My_Copy»: Application.OnKey «^w», «My_Paste» End Sub

Теперь можно скопировать нужный диапазон, нажав Ctrl+q, и вставить его в отфильтрованный диапазон, нажав Ctrl+w.

(46,5 KiB, 9 622 скачиваний)

Копируем только видимые ячейки и вставляем только в видимые
По просьбам посетителей сайта решил доработать данную процедуру. Теперь возможно копировать любые диапазоны: со скрытыми строками, скрытыми столбцами и вставлять скопированные ячейки также в любые диапазоны: со скрытыми строками, скрытыми столбцами. Работает совершенно так же, как и предыдущий: нажатием клавиш Ctrl+q копируем нужный диапазон(со скрытыми/отфильтрованными строками и столбцами или не скрытыми) , а вставляем сочетанием клавиш Ctrl+w. Вставка производится так же в скрытые/отфильтрованные строки и столбцы или без скрытых.
Если в копируемом диапазоне присутствуют формулы, то во избежание смещения ссылок можно копировать только значения ячеек — т.е. при вставке значений будут вставлены не формулы, а результат их вычисления. Или если необходимо сохранить форматы ячеек, в которые происходит вставка — будут скопированы и вставлены только значения ячеек. Для этого надо заменить строку в коде(в файле ниже):

Читайте так же:
Как быстро посчитать явки через каждые 15 минут в Excel?
rCell.Copy rResCell.Offset(lr, lc)

rCell.Copy rResCell.Offset(lr, lc)

rResCell.Offset(lr, lc) = rCell.Value

rResCell.Offset(lr, lc) = rCell.Value

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

(54,5 KiB, 7 928 скачиваний)

Помогла ли статья? Поделитесь ссылкой с друзьями! Видеоуроки

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

Выполните следующие действия.

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

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

Однако если отформатировать данные в виде таблицы и применить фильтр, то можно копировать и вставлять только видимые ячейки.

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

Дополнительные сведения

Вы всегда можете задать вопрос в сообществе Excel Tech Community, попросить помощи в сообществе Answers или предложить новую функцию или улучшение на веб-сайте.

Читайте так же:
Как быстро преобразовать файл CSV в столбцы в Excel?

Эксель промежуточные итоги формула – Промежуточные итоги в Excel с примерами функций

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ — Служба поддержки Office

В этой статье описывается синтаксис формулы и как использовать функцию СУММ в Microsoft Excel.

Описание

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

Синтаксис

Ниже приведен список аргументов функции TOTALS.

Номер_функции (обязательный). Число от 1 до 11 или от 101 до 111, которое обозначает функцию, используемую для расчета промежуточных итогов. Функции с 1 по 11 учитывают строки, скрытые вручную, в то время как функции с 101 по 111 пропускают такие строки; отфильтрованные ячейки всегда исключаются.

Номер_функции
(с включением скрытых значений)

Номер_функции
(с исключением скрытых значений)

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

Ссылка2;… Необязательный. Именованные диапазоны или ссылки 2—254, для которых требуется вычислить промежуточные итоги.

Примечания

Если уже имеются формулы подведения итогов внутри аргументов «ссылка1;ссылка2;…» (вложенные итоги), то эти вложенные итоги игнорируются, чтобы избежать двойного суммирования.

Для констант «номер_функции» от 1 до 11 функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ учитывает значения строк, скрытых с помощью команды Скрыть строки (меню Формат, подменю Скрыть или отобразить) в группе Ячейки на вкладке Главная в настольном приложении Excel. Эти константы используются для получения промежуточных итогов с учетом скрытых и нескрытых чисел списка. Для констант «номер_функции» от 101 до 111 функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ исключает значения строк, скрытых с помощью команды Скрыть строки. Эти константы используются для получения промежуточных итогов с учетом только нескрытых чисел списка.

Читайте так же:
Как быстро вставить путь к файлу в Excel?

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ исключает все строки, не включенные в результат фильтра, независимо от используемого значения константы «номер_функции».

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ применяется к столбцам данных или вертикальным наборам данных. Она не предназначена для строк данных или горизонтальных наборов данных. Так, при определении промежуточных итогов горизонтального набора данных с помощью значения константы «номер_функции» от 101 и выше (например, ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;B2:G2)), скрытие столбца не повлияет на результат. Однако на него повлияет скрытие строки при подведении промежуточного итога для вертикального набора данных.

Если среди ссылок есть трехмерные ссылки, функция TOTALS.TOTALS возвращает значение ошибки #ЗОНА!

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector