Як у Excel скопіювати лише видимі рядки. Копіювання лише видимих ​​осередків. Швидкий перехід до потрібного листа

Відбір даних 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 копіює не лише видимі, але й приховані чи фільтровані осередки. Якщо потрібно скопіювати лише видимі осередки, виконайте дії, описані нижче. Наприклад, можна скопіювати лише зведені дані із структурованого аркуша.

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

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

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

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

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

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

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

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

У цій статті мені хотілося б представити вам найефективніші прийоми роботи в Microsoft Excel, зібрані мною за останні 10 років роботи над проектами та проведення тренінгів з цієї чудової програми. Тут немає опису суперскладних технологій, але є прийоми на кожен день - прості та ефективні, описані без "води" - лише "сухий залишок". На освоєння більшості цих прикладів у вас піде не більше однієї-двох хвилин, а ось заощадити вони вам допоможуть набагато більше.

Швидкий перехід до потрібного листа

Чи трапляється вам працювати з книгами Excel, що складаються з великої кількості аркушів? Якщо їх більше десятка, то кожен перехід до чергового потрібного листа сам собою стає маленькою проблемою. Просте і елегантне рішення такого завдання - клацнути в лівому нижньому кутку вікна по кнопках прокручування ярликів аркушів не лівою, а правою кнопкою миші - з'явиться зміст книги з повним списком всіх аркушів і на потрібний аркуш можна буде перейти в один рух:

Це набагато швидше, ніж прокручувати ярлички аркушів цими ж кнопками у пошуках потрібного.

Копіювання без пошкодження форматування

Скільки сотень (тисяч?) раз я бачив цю картину, стоячи за спиною своїх слухачів під час тренінгів: користувач вводить формулу в перший осередок потім і "протягує" її на весь стовпець, порушуючи форматування нижчерозташованих рядків, оскільки такий спосіб копіює не тільки формулу, а й формат осередку. Відповідно, далі доводиться вручну виправляти ушкодження. Секунда на копіювання і потім 30 - на ремонт зіпсованого копіюванням дизайну.

Починаючи з Excel 2002, є вирішення цієї проблеми - просте та витончене. Відразу після копіювання (протягування) формули на весь стовпець, потрібно скористатися смарт-тегом - невеликим значком, який тимчасово з'являється в нижньому правому куті діапазону. Натискання на нього виведе список можливих варіантів копіювання, де можна вибрати Копіювати тільки значення (Fill without formatting). У цьому випадку формули копіюються, а форматування – ні:

Копіювання тільки видимих ​​осередків

Якщо ви працюєте в Microsoft Excel більше тижня, то мали вже стикатися з подібною проблемою: у деяких випадках при копіюванні-вставці осередків їх вставляється більше, ніж було, на перший погляд, скопійовано. Це може відбуватися, якщо діапазон, що копіюється, включав приховані рядки/стовпці, угруповання, проміжні підсумки або фільтрацію. Розглянемо як приклад один із таких випадків:

У цій таблиці пораховані проміжні підсумки та зроблено угруповання рядків по містах - це легко зрозуміти за кнопками "плюс-мінус" ліворуч від таблиці та розривами в нумерації видимих ​​рядків. Якщо виділити, скопіювати та вставити дані з цієї таблиці звичайним способом, то ми отримаємо 24 зайві рядки. Нам же хочеться скопіювати та вставити лише підсумки!

Можна вирішити проблему, ретельно виділяючи кожен рядок підсумків і утримуючи у своїй клавішу CTRL - як виділення несуміжних діапазонів. Але якщо таких рядків не три-п'ять, а кілька сотень чи тисяч? Є інший, швидший та зручний шлях:

Виділіть діапазон, що копіюється (у нашому прикладі - це A1:C29)

Натисніть на клавіатурі клавішу F5 і потім кнопку Виділити (Special) у вікні.
З'явиться вікно, що дозволяє користувачеві виділяти не всі поспіль, а лише потрібні осередки:

У цьому вікні виберіть опцію Тільки видимі комірки (Visible cells only) та натисніть OK.

Отримане виділення тепер можна сміливо копіювати та вставляти. В результаті ми отримаємо копію саме видимих ​​осередків і вставимо замість непотрібних 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, ввести ім'я діапазону без пробілів), а потім прописати це ім'я в полі