Вставити тільки у видимі рядки в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:a lpha(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))

Павлов Микола

У цій статті мені хотілося б представити вам самі ефективні прийомироботи в 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, можна відкрити книгу в ньому, щоб скопіювати та вставити видимі осередки. Для цього натисніть кнопку Відкрити в Excelі виконайте дії, описані у статті копіювання та вставлення тільки видимих ​​осередків .

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

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