Як вставити відфільтровані осередки в. Вставити у видимі рядки в Excel. Перетворення рядків на стовпці і назад
Павлов Микола
У цій статті мені хотілося б представити вам найефективніші прийоми роботи в Microsoft Excel, зібрані мною за останні 10 років роботи над проектами та проведення тренінгів за цією чудовою програмою. Тут немає опису суперскладних технологій, але є прийоми на кожен день - прості та ефективні, описані без "води" - лише "сухий залишок". На освоєння більшості з цих прикладів у вас піде не більше однієї-двох хвилин, а от заощадити вони вам допоможуть набагато більше.
Швидкий перехід до потрібного листа
Чи трапляється вам працювати з книгами Excel, Що складаються з великої кількості аркушів? Якщо їх більше десятка, то кожен перехід до чергового потрібного листа сам собою стає маленькою проблемою. Просте і елегантне рішення такого завдання - клацнути в лівому нижньому кутку вікна по кнопках прокручування ярликів листів не лівою, а правою кнопкою миші - з'явиться зміст книги з повним спискомвсіх листів і на потрібний лист можна буде перейти в один рух:
Це набагато швидше, ніж прокручувати ярлики аркушів цими ж кнопками у пошуках потрібного.
Копіювання без пошкодження форматування
Скільки сотень (тисяч?) раз я бачив цю картину, стоячи за спиною своїх слухачів під час тренінгів: користувач вводить формулу в першу комірку потім і "протягує" її на весь стовпець, порушуючи форматування рядків нижче, оскільки такий спосіб копіює не тільки формулу, а й формат осередку. Відповідно далі доводиться вручну виправляти пошкодження. Секунда на копіювання і потім 30 - на ремонт зіпсованого копіюванням дизайну.
Починаючи з Excel 2002, є вирішення цієї проблеми - просте та витончене. Відразу після копіювання (протягування) формули на весь стовпець, потрібно скористатися смарт-тегом - невеликим значком, який тимчасово з'являється в нижньому правому кутку діапазону. Натискання на нього виведе список можливих варіантівкопіювання, де і можна вибрати Копіювати лише значення (Fill without formatting). У цьому випадку формули копіюються, а форматування – ні:
Копіювання тільки видимих осередків
Якщо ви працюєте в Microsoft Excel більше тижня, то мали вже стикатися з подібною проблемою: у деяких випадках при копіюванні-вставці осередків їх вставляється більше, ніж було, на перший погляд, скопійовано. Це може відбуватися, якщо діапазон, що копіюється, включав приховані рядки/стовпці, угруповання, проміжні підсумки або фільтрацію. Розглянемо як приклад один із таких випадків:
У цій таблиці пораховано проміжні підсумки та зроблено угруповання рядків по містах - це легко зрозуміти за кнопками "плюс-мінус" ліворуч від таблиці та розривами в нумерації видимих рядків. Якщо виділити, скопіювати та вставити дані з цієї таблиці звичайним способом, то ми отримаємо 24 зайві рядки. Нам же хочеться скопіювати та вставити лише підсумки!
Можна вирішити проблему, ретельно виділяючи кожен рядок підсумків і утримуючи у своїй клавішу CTRL - як виділення несуміжних діапазонів. Але якщо таких рядків не три-п'ять, а кілька сотень чи тисяч? Є інший, швидший та зручний шлях:
Виділіть діапазон, що копіюється (у нашому прикладі - це A1:C29)
Натисніть на клавіатурі клавішу F5 і потім кнопку Виділити (Special) у вікні.
З'явиться вікно, що дозволяє користувачеві виділяти не всі поспіль, а лише потрібні комірки:
У цьому вікні виберіть опцію Тільки видимі комірки (Visible cells only) та натисніть ОК.
Отримане виділення тепер можна сміливо копіювати та вставляти. В результаті ми отримаємо копію саме видимих осередківі вставимо замість непотрібних 29 тільки необхідні нам 5 рядків.
Якщо є підозра, що подібну операцію вам доведеться робити часто, то є сенс додати на панель інструментів Microsoft Excel кнопку для швидкого виклику такої функції. Це можна зробити через меню Сервіс>Налаштування (Tools> Customize), потім перейти на вкладку Команди (Commands), у категорії Правка (Edit) знайти кнопку Виділити видимі комірки (Select visible cells) та перенести її мишею на панель інструментів:
Перетворення рядків на стовпці і назад
Проста операція, але якщо не знати як зробити її правильно - можна витратити півдня на перетягування окремих осередків вручну:
Насправді, все просто. У тій частині вищої математики, яка описує матриці, є поняття транспонування - дії, що змінює рядки і стовпці в матриці місцями один з одним. У Microsoft Excel це реалізується в три рухи: Копіюємо таблицю
Клацаємо правою кнопкою миші по порожньому осередку і вибираємо команду Спеціальна вставка (Paste Special)
У вікні ставимо прапор Транспонувати (Transpose) і тиснемо ОК:
Швидке додавання даних до діаграми
Уявімо собі просту ситуацію: у вас є звіт за минулий місяць із наочною діаграмою. Завдання – додати до діаграми нові числові дані вже за цей місяць. Класичний шлях її вирішення – це відкрити вікно джерела даних для діаграми, де додати новий ряд даних, ввівши його ім'я та виділивши діапазон із потрібними даними. Причому найчастіше сказати це легше, ніж зробити – все залежить від складності діаграми.
Інший шлях - простий, швидкий і красивий - виділити осередки з новими даними, скопіювати їх (CTRL+C) і вставити (CTRL+V) у діаграму. Excel 2003, на відміну від пізніших версій, підтримує навіть можливість перетягування виділеного діапазону осередків із даними та закидання його прямо в діаграму за допомогою миші!
Якщо хочеться контролювати всі нюанси та тонкощі, то можна використовувати не звичайну, а спеціальну вставку, вибравши в меню Правка>Спеціальна вставка (Edit> Paste Special). У цьому випадку Microsoft Excel відобразить діалогове вікно, що дозволяє налаштувати куди і як саме будуть додані нові дані:
Подібним чином можна легко створити діаграму, використовуючи дані з різних таблиць з різних листів. На виконання того ж завдання класичним способом піде набагато більше часу та сил.
Заповнення порожніх осередків
Після вивантаження звітів з деяких програм формат Excelабо при створенні зведених таблиць користувачі часто отримують таблиці з порожніми осередками деяких стовпцях. Ці пропуски не дозволяють застосовувати до таблиць звичні та зручні інструменти типу автофільтра та сортування. Природно виникає необхідність заповнити порожнечі значеннями з вищестоящих осередків:
Безумовно, при невеликій кількості даних, це легко можна зробити простим копіюванням - вручну простягнувши кожну заголовну комірку в стовпці А вниз на порожні комірки. А якщо в таблиці кілька сотень чи тисяч рядків та кілька десятків міст?
Є спосіб вирішити це завдання швидко і красиво за допомогою однієї формули:
Виділіть усі осередки в стовпці з порожнечами (тобто діапазон A1:A12 у нашому випадку)
Щоб у виділенні залишилися тільки порожні комірки, натисніть клавішу F5 і у вікні переходів - кнопку Виділити. Побачите вікно, що дозволяє вибрати - які саме осередки ми хочемо виділити:
Встановіть перемикач у положення Пусті (Blank) та натисніть OK. Тепер у виділенні мають залишитися лише порожні осередки:
Не змінюючи виділення, тобто. не чіпаючи мишу, введемо формулу в першу виділену комірку (А2). Натисніть на клавіатурі на знак "рівно", а потім на "стрілку вгору". Отримаємо формулу, яка посилається на попередній осередок:
Щоб ввести створену формулу відразу у всі виділені порожні комірки, натисніть клавішу ENTER, а поєднання CTRL+ ENTER. Формула заповнить усі порожні осередки:
Тепер залишилося лише замінити формули значення для фіксації результатів. Виділіть діапазон A1:A12, скопіюйте його та вставте в комірки їх значення, використовуючи спеціальну вставку.
Випадаючий список у осередку
Прийом, який без перебільшення повинен знати кожен, хто працює в Excel. Його застосування здатне поліпшити практично будь-яку таблицю незалежно від її призначення. На всіх тренінгах я намагаюся показати його своїм слухачам першого ж дня.
Ідея дуже проста - у всіх випадках, коли ви повинні ввести дані з будь-якого набору, замість ручного введення в комірку з клавіатури вибирати потрібне значення мишею зі списку:
Вибір товару з прайс-листа, імені клієнта із клієнтської бази, ПІБ співробітника зі штатного розкладу тощо. Варіантів застосування цієї функції безліч.
Щоб створити список, що випадає в осередку:
Виділіть комірки, в яких ви хочете створити список, що випадає.
Якщо у вас Excel 2003 або старше, виберіть у меню Дані>Перевірка (Data>Validation). Якщо у вас Excel 2007/2010, перейдіть на вкладку Дані (Data) і натисніть кнопку Перевірка даних (Data validation).
У вікні виберіть варіант Список (List) з списку, що розкривається.
У полі Джерело (Source) треба вказати значення, які мають бути у списку. Тут можливі варіанти:
Вписати в поле текстові варіанти через точку з комою
Якщо діапазон осередків з вихідними значеннями знаходиться на поточному аркуші - достатньо його виділити мишею.
Якщо він знаходиться на іншому аркуші цієї книги, йому доведеться заздалегідь дати ім'я (виділити комірки, натиснути CTRL+F3, ввести ім'я діапазону без пробілів), а потім прописати це ім'я в полі
Вставити тільки у видимі рядки в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».
Відбір даних Excel провели за допомогою фільтра або сортування. Тепер їх потрібно роздрукувати чи перенести до іншого місця. Копіювання в Excelналаштовано так, що копіюються і приховані осередки.
Розглянемо два способи, до
копіювати відфільтровані рядки в Excel.
Перший метод.
Є чудова функція в Excel - функція «Спеціальна вставка» Excel.
Отже, ми маємо таблицю.
Як встановити фільтр, дивіться у статтіФільтр в Excel.
Ми прибираємо фільтр зі списку всіх Іванових. Вийшло так.
Виділяємо таблицю, натискаємо у контекстному меню "Копіювати". Натискаємо лівою мишкою комірку А9, вибираємо в контекстному меню значення.
Натискаємо "ОК". Вуаль. Скопіювалося як значення видимих рядків, а й формат осередків.
Є один нюанс- Вставляти відфільтровані дані не в рядки, де стоїть фільтр. Наприклад, у прикладі - над рядки 1-7, а нижче чи іншому листі, т.д. Якщо вставимо в рядки, де стоїть фільтр, то вставляться відфільтровані дані і приховані фільтром рядки. Втім, вийде каша. 
Другий спосіб.
Таблиця та сама. Виділити таблицю з фільтрованими даними. На закладці «Головна» натискаємо у розділі «Редагування» кнопку функції «Знайти та виділити» в Excel. Потім натискаємо кнопку «Перейти». У діалоговому вікні, що з'явилося, натискаємо кнопку «Виділити…». У вікні «Виділення групи осередків» ставимо галочку біля рядка «тільки видимі комірки». Натискаємо "ОК". Тепер на цій же виділеній таблиці правою мишкою викликаємо контекстне меню. Натискаємо функцію "Копіювати". У новому місці (у нашому прикладі – це осередок А15) натискаємо «Вставити». Всі. Вийшло так.
Як, не копіюючи, одразу надрукувати дані фільтра в Excel, дивіться у статті "Закладка листа Excel"Розмітка сторінки""
Списки та діапазони (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 Це не 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 = .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 ).Row Next rCell Next iCol Application.ScreenUpdating = True : Application.Calculation = iCalculation End Sub |
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) |
rCell.Copy rResCell.Offset(lr, lc)
на таку:
| rResCell.Offset(lr, lc) = rCell.Value |
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;"textbgcss":"display:bsol; ; background-color:#333333; opacity:0.6; 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))
