Відбір даних Excel провели за допомогою фільтра або сортування. Тепер їх потрібно роздрукувати чи перенести до іншого місця. Копіювання в Excelналаштовано так, що копіюються і приховані осередки.
Розглянемо два способи, до копіювати відфільтровані рядки в Excel.
Перший метод.
Є чудова функція в Excel – функція « Спеціальна вставка» в Excel.
Отже, ми маємо таблицю.
Як встановити фільтр, дивіться у статтіФільтр в Excel.
Ми прибираємо фільтр зі списку всіх Іванових. Вийшло так.
Виділяємо таблицю, натискаємо у контекстному меню "Копіювати". Натискаємо лівою мишкою комірку А9, вибираємо в контекстному меню значення.
Натискаємо "ОК". Вуаль. Скопіювалося як значення видимих ​​рядків, а й формат осередків.
Є один нюанс- Вставляти відфільтровані дані не в рядки, де стоїть фільтр. Наприклад, у прикладі - над рядки 1-7, а нижче чи іншому листі, т.д. Якщо вставимо в рядки, де стоїть фільтр, то вставляться відфільтровані дані і приховані фільтром рядки. Втім, вийде каша. Другий спосіб.
Таблиця та сама. Виділити таблицю з фільтрованими даними. На закладці «Головна» натискаємо у розділі «Редагування» кнопку функції «Знайти та виділити» в Excel. Потім натискаємо кнопку «Перейти». У діалоговому вікні, що з'явилося, натискаємо кнопку «Виділити…». У вікні «Виділення групи осередків» ставимо галочку біля рядка «тільки видимі осередки». Натискаємо "ОК". Тепер на цій же виділеній таблиці правою мишкоювикликаємо контекстне меню. Натискаємо функцію "Копіювати". У новому місці (у нашому прикладі – це осередок А15) натискаємо «Вставити». Всі. Вийшло так.
Як, не копіюючи, одразу надрукувати дані фільтра в Excel, дивіться у статті "Закладка листа Excel"Розмітка сторінки""

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

Ще один спосіб викликати діалогове вікно "Виділення групи осередків".На закладці «Головна» у розділі «Редагування» натискаємо на кнопку «Знайти та виділити». У списку натискаємо на функцію «Виділення групи осередків».

Щоб заповнити видимі осередки у виділених стовпцях Excel , натискаємо поєднання клавіш «Ctrl» + D. І всі виділені стовпці заповняться даними або формулою, як у першому осередку. У нашому прикладі ми написали число800 в клітинку D2 стовпця D.

Як вставити скопійовані комірки тільки у видимі/відфільтровані комірки

Загалом сенс статті вже, гадаю, зрозумілий із назви. Просто трохи розширю.

Ні для кого не секрет, що 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: 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). 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( "^q", "My_Copy": Application.OnKey "^w", "My_Paste" End Sub

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

Завантажити приклад

(46,5 KiB, 9622 завантажень)

Копіюємо тільки видимі комірки та вставляємо тільки у видимі комірки
На прохання відвідувачів сайту вирішив доопрацювати цю процедуру. Тепер можна копіювати будь-які діапазони: із прихованими рядками, прихованими стовпцями і вставляти скопійовані осередки також у будь-які діапазони: зі прихованими рядками, прихованими стовпцями. Працює так само, як і попередній: натисканням клавіш Ctrl + q копіюємо потрібний діапазон (з прихованими/відфільтрованими рядками та стовпцями або не прихованими), а вставляємо поєднанням клавіш Ctrl + w . Вставка проводиться також у приховані/відфільтровані рядки та стовпці або без прихованих.
Якщо копіюваному діапазоні присутні формули, те щоб уникнути зміщення посилань можна копіювати лише значення осередків - тобто. при вставці значень буде вставлено не формули, а результат їх обчислення. Або якщо необхідно зберегти формати осередків, в які відбувається вставка, будуть скопійовані та вставлені лише значення осередків. Для цього треба замінити рядок у коді (у файлі нижче):

rCell.Copy rResCell.Offset(lr, lc)

на таку:

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

У файлі нижче обидві ці рядки присутні, Вам треба лише залишити ту, яка більше підходить під Ваші завдання.

Завантажити приклад:

(54,5 KiB, 7 928 завантажень)

Також див:
[]

("Bottom bar":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left"," :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","text ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"text textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; background-color:#333333;opacity:0.6;filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px "Lucida Sans Unicode", "Lucida Grande", sans-serif, Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px "Lucida Sans Unicode", "Lucida Grande", sans-serif, Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssress "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))

Якщо деякі осередки, рядки або стовпці на аркуші не відображаються, ви зможете скопіювати всі осередки (або тільки видимі осередки). За промовчанням Excel копіює не тільки видимі, але й приховані або фільтровані осередки. Якщо потрібно скопіювати лише видимі комірки, виконайте дії, описані нижче. Наприклад, можна скопіювати лише зведені дані із структурованого аркуша.

Виконайте наведені нижче дії.

Примітка:При копіюванні значення послідовно вставляються в рядки та стовпці. Якщо область вставки містить приховані рядки або стовпці, можливо, потрібно відобразити їх, щоб побачити всі скопійовані дані.

При копіюванні та вставці видимих ​​осередків у діапазоні даних, який містить приховані осередки або до якого застосований фільтр, можна помітити, що приховані осередки вставляються разом із видимими. На жаль, ви не можете змінити цей параметр, коли ви копіюєте та вставляєте діапазон осередків в Excel в Інтернеті, оскільки Вставка лише видимих ​​осередків недоступна.

Проте, якщо відформатувати дані як таблицю та застосувати фільтр, можна скопіювати та вставити лише видимі комірки.

Якщо не потрібно форматувати дані як таблицю та встановлено класичне додаток Excel, можна відкрити книгу в ньому, щоб скопіювати та вставити видимі осередки. Для цього натисніть кнопку Відкрити в Excelта виконайте дії, описані у статті копіювання та вставка тільки видимих ​​осередків.

додаткові відомості

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