Якщо деякі осередки, рядки або стовпці на аркуші не відображаються, ви зможете скопіювати всі осередки (або тільки видимі осередки). За замовчуванням Excel копіює не тільки видимі, але і приховані або Фільтровані осередки. Якщо ж потрібно скопіювати тільки видимі осередки, виконайте дії, описані нижче. Наприклад, можна скопіювати тільки зведені дані зі структурованого листа.
Виконайте такі дії.
Примітка: При копіюванні значення послідовно вставляються в рядки і стовпці. Якщо область вставки містить приховані рядки або стовпці, можливо, буде потрібно відобразити їх, щоб побачити всі скопійовані дані.
При копіюванні і вставці видимих \u200b\u200bосередків в діапазоні даних, який містить приховані осередки або до якого застосовано фільтр, можна помітити, що приховані осередки вставляються разом з видимими. На жаль, ви не можете змінити цей параметр, коли ви копіюєте та вставляєте діапазон комірок в Excel в Інтернеті, так як Вставка тільки видимих \u200b\u200bосередків недоступна.
Проте, якщо відформатувати дані як таблицю і застосувати фільтр, можна скопіювати і вставити тільки видимі осередки.
Якщо не потрібно форматувати дані як таблицю і встановлено класичне додаток Excel, можна відкрити книгу в ньому, щоб скопіювати і вставити видимі осередки. Для цього натисніть кнопку Відкрити в Excel і виконайте дії, описані в статті копіювання та вставка тільки видимих \u200b\u200bосередків.
додаткові відомості
Ви завжди можете надіслати електронною поштою питання Excel Tech Community, попросити допомоги в співтоваристві Answers community, а також запропонувати нову функцію або поліпшення на веб-сайті
Списки і діапазони (5)
Макроси (VBA процедури) (63)
Різне (39)
Баги і глюки Excel (4)
Як вставити скопійовані комірки тільки в видимі / відфільтровані осередки
В общем-то сенс статті вже, думаю, зрозумілий з назви. Просто трохи розширю.
Ні для кого не секрет, що Excel дозволяє виділити тільки видимі рядки (Наприклад, якщо деякі з них приховані або застосований фільтр).
Так ось, якщо скопіювати таким чином тільки видимі осередки, то скопійовано вони як годиться. Але при спробі вставити скопійоване в діапазон відфільтрований (або містить приховані рядки) - то результат вставки буде не зовсім такою, як Ви очікували. Дані будуть вставлені навіть в приховані рядки.
Копіюємо єдиний діапазон комірок і вставляємо тільки в видимі
Щоб дані вставлялися тільки в видимі осередки, можна застосувати такий макрос:
| Option Explicit Dim rCopyRange As Range "Цим макросом копіюємо дані Sub My_Copy () If Selection.Count\u003e 1 Then Set rCopyRange \u003d Selection.SpecialCells (xlVisible) Else: Set rCopyRange \u003d ActiveCell End If End Sub "Цим макросом вставляємо дані, починаючи з виділеної комірки Sub My_Paste () If rCopyRange Is Nothing Then Exit Sub If rCopyRange.Areas.Count\u003e 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 \u003d False iCalculation \u003d Application.Calculation: Application.Calculation \u003d -4135 For iCol \u003d 1 To rCopyRange .Columns.Count li \u003d 0: lCount \u003d 0: le \u003d iCol - 1 For Each rCell In rCopyRange.Columns (iCol) .Cells Do If ActiveCell.Offset (li, le) .EntireColumn.Hidden \u003d False And _ ActiveCell.Offset (li, le) .EntireRow.Hidden \u003d False Then rCell.Copy ActiveCell.Offset (li, le): lCount \u003d lCount + 1 End If li \u003d li + 1 Loop While lCount\u003e \u003d rCell.Row - rCopyRange.Cells (1 ) .Row Next rCell Next iCol Application.ScreenUpdating \u003d True: Application.Calculation \u003d iCalculation End Sub |
Option Explicit Dim rCopyRange As Range "Цим макросом копіюємо дані Sub My_Copy () If Selection.Count\u003e 1 Then Set rCopyRange \u003d Selection.SpecialCells (xlVisible) Else: Set rCopyRange \u003d ActiveCell End If End Sub" Цим макросом вставляємо дані, починаючи з виділеної осередки Sub My_Paste () If rCopyRange Is Nothing Then Exit Sub If rCopyRange.Areas.Count\u003e 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 \u003d False iCalculation \u003d Application.Calculation: Application.Calculation \u003d -4135 For iCol \u003d 1 To rCopyRange.Columns.Count li \u003d 0: lCount \u003d 0: le \u003d iCol - 1 For Each rCell In rCopyRange.Columns (iCol) .Cells Do If ActiveCell.Offset (li, le) .EntireColumn.Hidden \u003d False And _ ActiveCell.Offset (li, le) .EntireRow.Hidden \u003d False Then rCell.Copy ActiveCell.Offset (li, le): lCount \u003d lCount + 1 End If li \u003d li + 1 Loop While lCount\u003e \u003d rCell.Row - rCopyRange.Cells (1) .Row Next rCell Next iCol Application.ScreenUpdating \u003d True: Application.Calculation \u003d 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
. Вставка проводиться так само в приховані / відфільтровані рядки і стовпці або без прихованих.
Якщо в копійованому діапазоні присутні формули, то щоб уникнути зсуву посилань можна копіювати тільки значення осередків - тобто при вставці значень будуть вставлені не формулою, а результат їх обчислення. Або якщо необхідно зберегти формати осередків, в які відбувається вставка - будуть скопійовані і вставлені тільки значення осередків. Для цього треба замінити рядок в коді (в файлі нижче):
| rCell.Copy rResCell.Offset (lr, lc) |
rCell.Copy rResCell.Offset (lr, lc)
на таку:
| rResCell.Offset (lr, lc) \u003d rCell.Value |
rResCell.Offset (lr, lc) \u003d 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 "," texteffectslidedistance " : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "right", "texteffectslidedistance1": 120, "texteffecteasing1": "easeOutCubic", "texteffectduration1": 600 , "texteffectdelay1" 1000, "texteffect2": "slide", "texteffectslidedirection2": "right", "texteffectslidedistance2": 120, "texteffecteasing2": "easeOutCubic", "texteffectduration2": 600, "texteffectdelay2": 1500, " textcss ":" display: block; padding: 12px; text-align: left; "," textbgcss ":" display: block; position: absolute; top: 0px; left: 0px; width: 100%; height: 100% ; background-color: # 333333; opacity: 0.6; filter: a lpha (opacity \u003d 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; "," buttoncssresponsive ": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40))
Павлов Микола
У цій статті мені хотілося б представити вам найефективніші прийоми роботи в Microsoft Excel, Зібрані мною за останні 10 років роботи над проектами та проведення тренінгів по цій чудовій програмі. Тут немає опису суперскладних технологій, але є прийоми на кожен день - прості та ефективні, описані без "води" - тільки "сухий залишок". На освоєння більшості з цих прикладів у вас піде не більше однієї-двох хвилин, а ось заощадити вони вам допоможуть набагато більше.
Швидкий перехід до потрібного листу
Чи трапляється вам працювати з книгами Excel, що складаються з великої кількості листів? Якщо їх більше десятка, то кожен перехід до чергового потрібного листу сам по собі стає маленькою проблемою. Просте і елегантне рішення такого завдання - клацнути в лівому нижньому кутку вікна по кнопках прокручування ярликів аркушів не лівою, а правою кнопкою миші - з'явиться зміст книги з повним списком всіх листів і на потрібний лист можна буде перейти в один рух:
Це набагато швидше, ніж прокручувати ярлички листів цими ж кнопками в пошуках потрібного.
Копіювання без пошкодження форматування
Скільки сотень (тисяч?) Раз я бачив цю картину, стоячи за спиною своїх слухачів під час тренінгів: користувач вводить формулу в перший осередок потім і "простягає" її на весь стовпець, порушуючи форматування нижчих рядків, оскільки такий спосіб копіює не тільки формулу, але і формат осередку. Відповідно, далі доводиться вручну виправляти ушкодження. Секунда на копіювання та потім 30 - на лагодження зіпсованого копіюванням дизайну.
Починаючи з Excel 2002 є рішення цієї проблеми - просте і витончене. Відразу після копіювання (протягування) формули на весь стовпець, потрібно скористатися смарт-тегом - невеликим значком, який тимчасово з'являється в правому нижньому кутку діапазону. Натискання на нього виведе список можливих варіантів копіювання, де і можна вибрати Копіювати тільки значення (Fill without formatting). В цьому випадку формули копіюються, а форматування - немає:
Копіювання тільки видимих \u200b\u200bосередків
Якщо ви працюєте в Microsoft Excel більше тижня, то повинні були вже мати справу з подібною проблемою: в деяких випадках при копіюванні-вставці осередків їх вставляється більше, ніж було, на перший погляд, скопійовано. Це може відбуватися, якщо потрібно скопіювати діапазон включав в себе приховані рядки / стовпці, угруповання, проміжні підсумки або фільтрацію. Розглянемо як приклад один з таких випадків:
У цій таблиці пораховані проміжні підсумки і зроблена угруповання рядків по містах - це легко зрозуміти по кнопках "плюс-мінус" зліва від таблиці і по розривах в нумерації видимих \u200b\u200bрядків. Якщо виділити, скопіювати і вставити дані з цієї таблиці звичайним способом, то ми отримаємо 24 зайвих рядки. Нам же хочеться скопіювати і вставити тільки підсумки!
Можна вирішити проблему, ретельно виділяючи кожен рядок підсумків і утримуючи при цьому клавішу CTRL - як для виділення несуміжних діапазонів. Але якщо таких рядків не три-п'ять, а кілька сотень або тисяч? Є інший, більш швидкий і зручний шлях:
Виділіть копійований діапазон (в нашому прикладі - це A1: C29)
Натисніть на клавіатурі клавішу F5 і потім кнопку Виділити (Special) у вікні.
З'явиться вікно, що дозволяє користувачеві виділяти не всі підряд, а тільки потрібні осередки:
У цьому вікні виберіть опцію Тільки видимі осередки (Visible cells only) і натисніть ОК.
Отримане виділення тепер можна сміливо копіювати і вставляти. В результаті ми отримаємо копію саме видимих \u200b\u200bосередків і вставимо замість непотрібних 29-ти тільки необхідні нам 5 рядків.
Якщо є підозра, що подібну операцію вам доведеться проробляти часто, то має сенс додати на панель інструментів Microsoft Excel кнопку для швидкого виклику такої функції. Це можна зробити через меню Сервіс\u003e Налаштування (Tools\u003e Customize), потім перейти на вкладку Команди (Commands), в категорії Правка (Edit) знайти кнопку Виділити видимі осередки (Select visible cells) і перенести її мишкою на панель інструментів:
Перетворення рядків у стовпці і назад
Проста операція, але якщо не знати як зробити її правильно - можна витратити півдня на перетягування окремих осередків вручну:
Насправді все просто. У тій частині вищої математики, Яка описує матриці є поняття транспонування - дії, яке змінює рядки і стовпці в матриці місцями один з одним. У Microsoft Excel це реалізується в три руху: Копіюємо таблицю
Клацаємо правою кнопкою миші по порожньому осередку і вибираємо команду Спеціальна вставка (Paste Special)
У вікні ставимо прапор Транспонувати (Transpose) і тиснемо ОК:
Швидке додавання даних в діаграму
Уявімо собі просту ситуацію: у вас є звіт за минулий місяць з наочною діаграмою. Завдання - додати в діаграму нові числові дані вже за цей місяць. Класичний шлях її вирішення - це відкрити вікно джерела даних для діаграми, де додати новий ряд даних, ввівши його ім'я і виділивши діапазон з потрібними даними. Причому найчастіше сказати це легше, ніж зробити - все залежить від складності діаграми.
Інший шлях - простий, швидкий і красивий - виділити комірки з новими даними, скопіювати їх (CTRL + C) і вставити (CTRL + V) прямо в діаграму. Excel 2003, на відміну від більш пізніх версій, підтримує навіть можливість перетягування виділеного діапазону комірок з даними і закидання його прямо в діаграму за допомогою миші!
Якщо хочеться контролювати всі нюанси і тонкощі, то можна використовувати не звичайну, а спеціальну вставку, вибравши в меню Правка\u003e Спеціальна вставка (Edit\u003e Paste Special). У цьому випадку Microsoft Excel відобразить діалогове вікно для встановлення куди і як саме будуть додані нові дані:
Подібним же чином можна легко створити діаграму, використовуючи дані з різних таблиць з різних листів. На виконання тієї ж завдання класичним способом піде набагато більше часу і сил.
Заповнення порожніх клітинок
Після вивантаження звітів з деяких програм в формат Excel або при створенні зведених таблиць користувачі часто отримують таблиці з порожніми осередками в деяких шпальтах. Ці пропуски не дозволяють застосовувати до таблицями звичні та зручні інструменти типу автофильтра і сортування. Природним чином виникає необхідність заповнити пустоти значеннями з вищестоящих осередків:
Безумовно, при невеликій кількості даних, це легко можна зробити простим копіюванням - вручну простягнувши кожну заголовну осередок в стовпці А вниз на порожні клітинки. А якщо в таблиці кілька сотень або тисяч рядків і кілька десятків міст?
Є спосіб вирішити цю задачу швидко і красиво за допомогою однієї формули:
Виділіть всі комірки в стовпці з пустотами (тобто діапазон A1: A12 в нашому випадку)
Щоб у виділенні залишилися тільки порожні клітинки, натисніть клавішу F5 і в вікні, переходів - кнопку Виділити. Побачите вікно, що дозволяє вибрати - які саме осередку ми хочемо виділити:
Виберіть пункт Порожні (Blank) і натисніть ОК. Тепер у виділенні повинні залишитися тільки порожні клітинки:
Не змінюючи виділення, тобто не чіпаючи миша, введемо формулу в першу виділену клітинку (А2). Натисніть на клавіатурі на знак "дорівнює" і потім на "стрілку вгору". Отримаємо формулу, яка посилається на попередній осередок:
Щоб ввести створену формулу відразу в усі виділені порожні клітинки, натисніть НЕ клавішу ENTER, а поєднання CTRL + ENTER. Формула заповнить всі порожні клітинки:
Тепер залишилося тільки замінити формули на значення для фіксації результатів. Виділіть діапазон A1: A12, скопіюйте його і вставте в осередку їх значення, використовуючи спеціальну вставку.
Список, що випадає в осередку
Прийом, який, без перебільшення, має знати кожен, хто працює в Excel. Його застосування здатне поліпшити, практично, будь-яку таблицю незалежно від її призначення. На всіх тренінгах я намагаюся показати його своїм слухачам в перший же день.
Ідея дуже проста - у всіх випадках, коли ви повинні ввести дані з будь-якого набору, замість ручного введення в осередок з клавіатури вибирати потрібне значення мишею зі списку:
Вибір товару з прайс-листа, імені клієнта з клієнтської бази, ПІБ співробітника зі штатного розпису і т.д. Варіантів застосування цієї функції безліч.
Щоб створити список, що випадає в осередку:
Виділіть комірки, в яких ви хочете створити список, що випадає.
Якщо у вас Excel 2003 або старше, то виберіть в меню Дані\u003e Перевірка (Data\u003e Validation). Якщо у вас Excel 2007/2010, то перейдіть на вкладку Дані (Data) і натисніть кнопку Перевірка даних (Data validation).
У вікні, виберіть варіант Список (List) із списку.
В поле Джерело (Source) треба вказати значення, які повинні бути в списку. Тут можливі варіанти:
Вписати в це поле текстові варіанти через крапку з комою
Якщо діапазон комірок з вихідними значеннями знаходиться на поточному аркуші - досить його просто виділити мишею.
Якщо він знаходиться на іншому аркуші цієї книги, то йому доведеться заздалегідь дати ім'я (виділити осередки, натиснути CTRL + F3, ввести ім'я діапазону без пробілів), а потім прописати це ім'я в поле
Відбір даних в Excel провели за допомогою фільтра або сортування. Тепер їх потрібно роздрукувати або перенести в інше місце. Копіювання в Excel налаштоване так, що копіюються і приховані осередки.
Розглянемо два способи, до
ак копіювати відфільтровані рядки в Excel.
Перший спосіб.
Є чудова функція в Excel - функція «Спеціальна вставка» в Excel.
Отже, у нас є таблиця.
Як встановити фільтр, дивіться в статті "Фільтр в Excel ".
Ми прибираємо фільтром зі списку всіх Іванових. Вийшло так.
Виділяємо таблицю, натискаємо в контекстному меню «Копіювати». Натискаємо лівої мишкою осередок А9, вибираємо в контекстному меню «значення».
Натискаємо «ОК». Вуаля. Скопіювати не тільки значення видимих \u200b\u200bрядків, але і формат осередків.
Є один нюанс - вставляти відфільтровані дані не в рядки, де стоїть фільтр. Наприклад, у нас в прикладі - не в рядки 1-7, а нижче або на іншому аркуші, тощо Якщо вставимо в рядки, де стоїть фільтр, то вставити відфільтровані дані і в приховані фільтром рядки. Вообщем, вийде каша. 
Другий спосіб.
Таблиця та ж. Виділити таблицю з відфільтрованими даними. На закладці «Головна» натискаємо в розділі «Редагування» кнопку функції «Знайти і виділити» в Excel. Потім, натискаємо кнопку «Перейти». У діалоговому вікні, натискаємо кнопку «Виділити ...». У вікні «Виділення групи осередків» ставимо галочку у рядки «тільки видимі осередки». Натискаємо «ОК». 
Тепер на цій же виділеної таблиці правою кнопкою миші викликаємо контекстне меню. Натискаємо функцію «Копіювати». У новому місці (в нашому прикладі - це осередок А15) натискаємо «Вставити». Усе. Вийшло так.
Як, що не копіюючи, відразу надрукувати дані фільтра в Excel, Дивіться в статті "Закладка листа Excel« Розмітка сторінки »"
