Како да изберете само видливи ќелии во ексел. Копирајте филтрирани податоци во Excel. Копирај без оштетување на форматирањето
Павлов Николај
Во оваа статија би сакал да ви ги претставам најефикасните техники за работа Microsoft Excel, собрани од мене во изминатите 10 години работа на проекти и спроведување на обуки за оваа прекрасна програма. Овде нема опис на супер сложени технологии, но има техники за секој ден - едноставни и ефективни, опишани без „вода“ - само „сув остаток“. Повеќето од овие примери ќе ви одземат не повеќе од една или две минути за да ги совладате, но ќе ви помогнат да заштедите многу повеќе.
Брзо скокнете до саканиот лист
Дали ви се случува да работите со Excel работни книгисе состои од голем број листови? Ако има повеќе од десетина од нив, тогаш секој премин кон следниот потребен лист станува мал проблем сам по себе. Едноставно и елегантно решение за овој проблем е да кликнете во долниот лев агол на прозорецот на копчињата за лизгање на јазичињата на листовите не со лево, туку со десното копче на глувчето - ќе се појави табела со содржина на книгата со целосна листасите листови и можете да отидете до саканиот лист со едно движење:
Ова е многу побрзо отколку да се движите низ картичките на листовите користејќи ги истите копчиња во потрага по она што ви треба.
Копирај без оштетување на форматирањето
Колку стотици (илјадници?) пати сум ја видел оваа слика како стои зад моите ученици за време на тренинзите: корисникот внесува формула во првата ќелија и потоа ја „растегнува“ низ целата колона, прекршувајќи го форматирањето на редовите подолу. бидејќи овој метод ја копира не само формулата, туку и форматот на ќелијата. Според тоа, тогаш треба рачно да ја поправите штетата. Секунда за копирање, а потоа 30 за поправка на дизајн оштетен со копирање.
Почнувајќи од Excel 2002, постои решение за овој проблем кое е едноставно и елегантно. Веднаш по копирањето (влечењето) на формулата на целата колона, треба да користите паметна ознака - мала икона што привремено се појавува во долниот десен агол на опсегот. Со кликнување на него ќе се појави список можни опциикопирање, каде што можете да изберете Пополнете без форматирање. Во овој случај, формулите се копираат, но форматирањето не е:
Се копираат само видливи ќелии
Ако работите во Microsoft Excel повеќе од една недела, сигурно веќе сте наишле на сличен проблем: во некои случаи, при копирање и вметнување ќелии, се вметнуваат повеќе ќелии отколку што, на прв поглед, биле копирани. Ова може да се случи ако копираниот опсег вклучува скриени редови/колони, групирања, подзбирови или филтрирање. Да земеме еден од овие случаи како пример:
Во оваа табела се пресметуваат потвкупите и редовите се групирани по град - ова е лесно да се разбере со копчињата плус-минус лево од табелата и со прекините во нумерирањето на видливите редови. Ако ги избереме, копираме и залепиме податоците од оваа табела на вообичаен начин, ќе завршиме со 24 дополнителни редови. Сакаме само да ги копираме и залепиме резултатите!
Можете да го решите проблемот со макотрпно избирање на секој ред од вкупните збирки додека го држите копчето CTRL - како што би правеле за избирање на несоседни опсези. Но, што ако нема три или пет такви линии, туку неколку стотици или илјадници? Постои уште еден, побрз и поудобен начин:
Изберете го опсегот за копирање (во нашиот пример е A1:C29)
Притиснете го копчето F5 на тастатурата и потоа копчето Изберете во прозорецот што се отвора.
Ќе се појави прозорец кој му овозможува на корисникот да избере не сè по ред, туку само потребните ќелии:
Во овој прозорец, изберете ја опцијата Само видливи ќелии и кликнете OK.
Добиениот избор сега може безбедно да се копира и залепи. Како резултат на тоа, ќе добиеме копија од точно видливи клеткии наместо непотребните 29 вметнете ги само 5-те линии што ни се потребни.
Ако се сомневате дека ќе мора често да вршите таква операција, тогаш има смисла да додадете копче во лентата со алатки на Microsoft Excel за брзо повикување на таква функција. Ова може да се направи преку менито Алатки> Прилагоди, потоа одете во табулаторот Команди, во категоријата Уреди, пронајдете го копчето Избери видливи ќелии и повлечете го до лентата со алатки со глувчето:
Конвертирање на редови во колони и повторно назад
Едноставна операција, но ако не знаете како да ја направите правилно, можете да поминете половина ден рачно влечејќи поединечни ќелии:
Тоа е всушност едноставно. Во тој дел од вишата математика што ги опишува матриците, постои концептот на транспозиција - дејство што ги заменува редовите и колоните во матрица едни со други. Во Microsoft Excel, ова се спроведува во три чекори: Копирајте ја табелата
Десен-клик на празна ќелија и изберете Вметни специјално.
Во прозорецот што се отвора, проверете го знамето Transpose и кликнете OK:
Брзо додавајте податоци на графиконот
Ајде да замислиме едноставна ситуација: имате извештај за минатиот месец со визуелен дијаграм. Задачата е да се додадат нови нумерички податоци на графиконот за овој месец. Класичниот начин да се реши ова е да се отвори прозорецот за извор на податоци за графиконот, каде што додавате нова серија на податоци со внесување на неговото име и означување на опсегот со саканите податоци. Покрај тоа, ова е често полесно да се каже отколку да се направи - сето тоа зависи од сложеноста на дијаграмот.
Друг начин - едноставен, брз и убав - е да ги изберете ќелиите со нови податоци, да ги копирате (CTRL+C) и да залепите (CTRL+V) директно во графиконот. Excel 2003, за разлика од подоцнежните верзии, дури ја поддржува можноста за влечење избран опсег на податочни ќелии и пуштање директно во графиконот со помош на глувчето!
Ако сакате да ги контролирате сите нијанси и суптилностите, тогаш можете да користите не обична, туку специјална паста со избирање на Edit> Paste Special од менито. Во овој случај, Microsoft Excel ќе прикаже дијалог-кутија што ви овозможува да конфигурирате каде и како точно ќе се додадат новите податоци:
Слично на тоа, можете лесно да креирате графикон користејќи податоци од различни табели од различни листови. Извршувањето на истата задача на класичен начин ќе потрае многу повеќе време и напор.
Пополнување празни ќелии
По преземањето извештаи од некои програми во Excel формат или кога креираат стожерни табели, корисниците често добиваат табели со празни ќелии во некои колони. Овие пропусти не ви дозволуваат да примените познати и удобни алатки како автофилтер и сортирање на табелите. Секако, има потреба да се пополнат празнините со вредности од ќелии од повисоко ниво:
Се разбира, со мала количина на податоци, ова може лесно да се направи со едноставно копирање - рачно влечење на секоја ќелија за заглавие во колоната А надолу кон празните ќелии. Што ако табелата има неколку стотици или илјадници редови и неколку десетици градови?
Постои начин да се реши овој проблем брзо и убаво користејќи една формула:
Изберете ги сите ќелии во колона со празни места (т.е. опсег A1:A12 во нашиот случај)
За да ги задржите само празните ќелии во изборот, притиснете го копчето F5 и во прозорецот за навигација што се отвора, притиснете го копчето Изберете. Ќе видите прозорец кој ви овозможува да изберете кои ќелии сакаме да ги избереме:
Поставете го прекинувачот на Blank и кликнете OK. Сега само празни ќелии треба да останат во изборот:
Без промена на изборот, т.е. Без допирање на глувчето, внесете ја формулата во првата избрана ќелија (A2). Притиснете го знакот за еднаквост на тастатурата, а потоа стрелката нагоре. Добиваме формула која се однесува на претходната ќелија:
За да ја внесете креираната формула во сите избрани празни ќелии одеднаш, не притиснете го копчето ENTER, туку комбинацијата CTRL + ENTER. Формулата ќе ги пополни сите празни ќелии:
Сега останува само да се заменат формулите со вредности за да се забележат резултатите. Изберете го опсегот A1:A12, копирајте го и залепете ги нивните вредности во ќелиите користејќи Paste Special.
Паѓачка листа во ќелија
Техника која без претерување треба да ја знае секој што работи во Excel. Неговата употреба може да ја подобри речиси секоја табела, без оглед на нејзината намена. На сите тренинзи се трудам тоа да им го покажам на моите ученици уште првиот ден.
Идејата е многу едноставна - во сите случаи кога мора да внесете податоци од кој било сет, наместо рачно да внесувате ќелија од тастатурата, изберете ја саканата вредност со глувчето од паѓачката листа:
Избор на производ од ценовникот, името на клиентот од базата на податоци на клиентите, целосното име на вработениот од табелата за персонал итн. Постојат многу опции за користење на оваа функција.
За да креирате паѓачка листа во ќелија:
Изберете ги ќелиите во кои сакате да креирате паѓачка листа.
Ако имате Excel 2003 или постар, изберете Data>Validation од менито. Ако имате Excel 2007/2010, тогаш одете во табулаторот Податоци и кликнете на копчето за валидација на податоци.
Во прозорецот што се отвора, изберете ја опцијата Листа од паѓачката листа.
Во полето Извор, мора да ги наведете вредностите што треба да бидат во списокот. Еве ги можните опции:
Внесете опции за текст во ова поле одделени со точка-запирка
Ако опсегот на ќелии со оригиналните вредности е на тековниот лист, само треба да го изберете со глувчето.
Ако се наоѓа на друг лист од оваа работна книга, тогаш ќе треба однапред да му дадете име (одберете ќелии, притиснете CTRL+F3, внесете го името на опсегот без празни места), а потоа напишете го ова име во полето
Ако некои ќелии, редови или колони во работниот лист не се видливи, можете да ги копирате сите ќелии (или само видливите ќелии). Стандардно, Excel копира не само видливи ќелии, туку и скриени или филтрирани ќелии. Ако сакате да копирате само видливи ќелии, следете ги чекорите подолу. На пример, можете да копирате само збирни податоци од структуриран работен лист.
Следете ги чекорите подолу.
Забелешка:Кога копирате, вредностите се вметнуваат последователно во редови и колони. Ако областа за залепување содржи скриени редови или колони, можеби ќе треба да ги откријат за да ги видите сите податоци што сте ги копирале.
Кога копирате и залепувате видливи ќелии во опсег на податоци што содржи скриени ќелии или на кој е применет филтер, може да забележите дека скриените ќелии се залепени заедно со видливите ќелии. За жал, не можете да ја промените оваа поставка кога копирате и залепувате опсег на ќелии во Excel за веб бидејќи Вметнувањето само видливи ќелии не е достапно.
Меѓутоа, ако ги форматирате вашите податоци како табела и примените филтер, можете да ги копирате и залепите само видливите ќелии.
Ако не треба да ги форматирате податоците како табела и да имате инсталирано класично Excel апликација, можете да отворите работна книга во неа за да копирате и залепите видливи ќелии. За да го направите ова, кликнете на копчето Отворете во Excelи следете ги чекорите во Копирање и залепување само видливи ќелии.
дополнителни информации
Секогаш можете да поставите прашање до специјалист за Excel Tech Community, да побарате помош во заедницата Answers, а исто така да предложите нова карактеристикаили подобрување на веб-страницата
Вметнете само во видливи линииExcelброеви, формули, текст може да се прават на неколку начини. Кога треба да вметнете броеви, формули, текст во не сите редови од табелата, можете да користите филтер. Како да инсталирате филтер и како да филтрирате во Excel, видете ја статијата „Филтер во Excel“. Но, за да вметнете податоци само во видливи ќелии, потребни ви се сопствени методи, особено ако има многу редови.
Првиот начин еобични .
Ајде да земеме ваква табела. Табелата ќе биде иста за сите примери.
Ајде да користиме филтер за да ги отстраниме сите цифри 2 од табелата. Во преостанатите видливи ќелии го ставаме бројот 600. Во ќелијата B2 го ставаме бројот 600, а потоа копирајте го надолу во колоната (повлечете го долниот десен агол на ќелијата B2). Вредностите беа копирани само во видливи ќелии. Можете исто така да вметнете формули на ист начин. Ја пишуваме следната формула во ќелијата C2. =A2*10
Испадна вака. 
Ајде да го откажеме филтерот. Резултатот е ваква табела.
Формулата и броевите беа вметнати само во филтрираните редови.
Втор начин.
Ќе ги филтрираме и податоците. Во првата ќелија пишуваме број, формула, текст итн. Сега, ако има илјадници редови, тогаш изберете ги ќелиите вака: притиснете ги копчињата „Ctrl“ + „Shift“ + копчето со стрелка надолу (или копчето нагоре, во зависност од тоа каде сакаме да ги избереме ќелиите - долу или над ќелијата во која е запишан бројот) .
Сега, или притиснете ја комбинацијата на копчиња „Ctrl“ + G или копчето F5. Ќе се појави полето за дијалог Транзиција. Кликнете на копчето „Избери...“. И, во новиот дијалог прозорец „Изберете група ќелии“, штиклирајте го полето до зборовите „Само видливи ќелии“.
Кликнете на „OK“. Потоа вметнете како и обично.
Друг начин да се појави прозорец за дијалог Избери група на ќелии.На табулаторот „Дома“, во делот „Уредување“, кликнете на копчето „Најди и изберете“. Во списокот што се појавува, кликнете на функцијата „Изберете група на ќелии“.
До пополнете видливи ќелии во избраните Колони на Excel , притиснете ја комбинацијата на копчиња „Ctrl“ + D. И сите избрани колони ќе бидат пополнети со податоци или формула, како во првата ќелија. Во нашиот пример, го напишавме бројот 800 во ќелијата D2, колона D.
Трет начин.
Во нова колона (во нашиот пример, колона Е), изберете ги ќелиите. Притиснете го копчето F5. Ќе се појави полето за дијалог Транзиција. Кликнете на копчето „Избери...“. И, во новиот дијалог прозорец „Изберете група ќелии“, штиклирајте го полето до зборовите „Само за видливи ќелии“. Кликнете на „OK“. Сега, без да го откажете изборот, во првата ќелија од колоната (нашата е Е2), внесете формула, број итн. Притиснете ја комбинацијата на копчиња „Ctrl“ + „Enter“.
Списоци и опсези (5)
Макроа (VBA процедури) (63)
Разно (39)
Грешки и грешки во Excel (4)
Како да залепите копирани ќелии само во видливи/филтрирани ќелии
Во принцип, значењето на статијата, мислам, е веќе јасно од насловот. Само ќе проширам малку.
Не е тајна дека Excel ви овозможува да изберете само видливи редови (на пример, ако некои од нив се скриени или се примени филтер).
Значи, ако на овој начин копирате само видливи ќелии, тие ќе бидат копирани како што се очекуваше. Но, кога ќе се обидете да залепите нешто копирано во филтриран опсег (или што содржи скриени редови), резултатот од залепувањето нема да биде точно каков што очекувавте. Податоците ќе бидат вметнати дури и во скриени редови.
Копирајте еден опсег на ќелии и залепете само во видливи
За да вметнете податоци само во видливи ќелии, можете да го користите следново макро:
| Опција Експлицитно затемнување rCopyRange како опсег „Со ова макро ги копираме податоцитеПод My_Copy() If Selection.Count > 1 Потоа поставете rCopyRange = Selection.SpecialCells(xlVisible) Друго : Поставете rCopyRange = ActiveCell End If End Sub „Со оваа макро вметнуваме податоци почнувајќи од избраната ќелијаПод My_Paste() Ако rCopyRange не е ништо, тогаш излезете од Sub If rCopyRange.Areas.Count > 1 Потоа MsgBox "Залепениот опсег не смее да содржи повеќе од еден регион!",vbКритички, „Неважечки опсег“: Излезете од Sub Dim rCell As Range, li As Long, le As Long, lCount As Long, iCol како цел број, iCalculation како цел број апликација. .Columns.Count li = 0: lCount = 0: le = iCol - 1 за секоја rCell во rCopyRange.Columns(iCol).Cells Do If ActiveCell.Offset(li, le).EntireColumn.Hidden = False и _ ActiveCell.Offset (li, le).EntireRow.Hidden = False Потоа rCell.Copy ActiveCell.Offset(li, le): lCount = lCount + 1 End If li = li + 1 Loop Додека lCount >= rCell.Row - rCopyRange.Cells(1 ).Ред Следна rCell Next iCol Application.ScreenUpdating = True : Application.Calculation = iCalculation End Sub |
Опција Експлицитна Dim rCopyRange како опсег "Користете го ова макро за да ги копирате податоците Sub My_Copy() If Selection.Count > 1 Потоа поставете rCopyRange = Selection.SpecialCells(xlVisible) Друго: Поставете rCopyRange = ActiveCell End If End Sub "Користете го ова макро за залепување податоците почнувајќи од избраните ќелии Sub My_Paste() Ако rCopyRange не е ништо, тогаш излезете од Sub If rCopyRange.Areas.Count > 1 Потоа MsgBox "Залепениот опсег не смее да содржи повеќе од една област!", vbCritical, "Невалиден опсег": Излезете Под затемнети rCell како опсег, li As Long, le As Long, lCount As Long, iCol како цел број, iCalculation како цел број Application.ScreenUpdating = Неточно iCalculation = Application.Calculation: Application.Calculation = -4135 За iCollumRange = 1 до r. .Count li = 0: lCount = 0: le = iCol - 1 за секоја rCell во rCopyRange.Columns(iCol).Cells Do If ActiveCell.Offset(li, le).EntireColumn.Hidden = False и _ ActiveCell.Offset(li , le).EntireRow.Hidden = False Потоа rCell.Copy ActiveCell.Offset(li, le): lCount = lCount + 1 End If li = li + 1 Loop Додека lCount >= rCell.Row - rCopyRange.Cells(1). Ред Следен rCell Next iCol Application.ScreenUpdating = Точно: Application.Calculation = iCalculation Крај Под
За да ја комплетирате сликата, подобро е да ги доделите овие макроа на топли копчиња (во шифрите подолу, тоа се прави автоматски кога отворате книга со кодот). За да го направите ова, само треба да ги копирате шифрите подолу во модулот Оваа книга (Оваа Работна тетратка) :
Опција Експлицитна „Откажи ја назначувањето на копчињата пред затворање на работната книга Приватна под работна книга_BeforeClose(Cancel as Boolean) Application.OnKey „^q“: Application.OnKey „^w“ Крај под „Додели копчиња при отворање на работната книга Приватна под Workbookp_Open() .OnKey "^q", "My_Copy": Application.OnKey "^w", "My_Paste" Крајна под
Сега можете да го копирате саканиот опсег со притискање на копчињата 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) = rCell.Value |
rResCell.Offset(lr, lc) = rCell.Value
И двете од овие редови се присутни во датотеката подолу; само треба да ја оставите онаа што е посоодветна за вашите задачи.
Преземи пример:
(54,5 KiB, 7.928 преземања)
Видете исто така:
[]
(„Долна лента“:(„текстстил“: „статички“, „текстпозиција статична“: „долу“, „текстаутоид“: точно, „текстпозицијамаргинастатична“:0, текстпозицијадинамика: „долно лево“, „текстпозицијамаргиналефт“:24,“ textpositionmarginright":24"textpositionmargintop":24,"textpositionmarginbottom":24,"textpositionmarginbottom":24,"texteffect":"слајд", "texteffecteasing":"easeOutCubic", "texteffectduration":600,"texteffectslidedirection":"лево", "texteffectefect" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"слајд","texteffectslidedirection1":"десно","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic,"texteffectslidedirection1"t "texteffectdelay1":1000,"texteffect2":"слајд", "texteffectslidedirection2":"десно", "texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic", "texteffectduration2":600effectdelayed"text textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; лево:0px; ширина:100%; висина:100% ; позадина-боја:#333333; непроѕирност:0,6; филтер:алфа(непроѕирност=60);","titlecss":"приказ:блок; позиција:роднина; фонт:задебелен 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; боја:#fff;","descriptioncss":"display:block; позиција:роднина; фонт:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; боја:#fff; margin-top:8px;","buttoncss":"display:block; позиција:роднина; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"големина на фонтот:12px;","descriptioncssresponsive":"display:нема !важно;","buttoncssresponsive" "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))
Изборот на податоци во Excel беше извршен со помош на филтер или сортирање. Сега тие треба да се испечатат или преместат на друга локација. Копирај во Excelконфигуриран така што и скриените ќелии се копираат.
Да разгледаме два начина, До
Како да копирате филтрирани редови во Excel.
Првиот начин.
Има одлична функција во Excel - Вметни Специјална функција во Excel.
Значи имаме маса.
Како да инсталирате филтер, видете ја статијата "Филтрирајте во Excel“.
Ние користиме филтер за да ги отстраниме сите Иванови од списокот. Испадна вака.
Изберете ја табелата и кликнете на „Копирај“ во контекстното мени. Лево-клик на ќелијата A9 и изберете „вредности“ во контекстното мени.
Кликнете на „OK“. Voila. Не беше копирана само вредноста на видливите редови, туку и форматот на ќелиите.
Има една нијанса- вметнете филтрирани податоци не во редовите каде што се наоѓа филтерот. На пример, во нашиот пример - не во редовите 1-7, туку подолу или на друг лист, итн. Ако вметнеме во редовите каде што се наоѓа филтерот, тогаш филтрираните податоци ќе бидат вметнати и во редовите скриени од филтерот. Во принцип, ќе испадне дека е хаос. 
Втор начин.
Табелата е иста. Изберете ја табелата со филтрирани податоци. На табулаторот „Дома“, кликнете во делот „Уредување“. Најдете и означете функции во Excel. Потоа, кликнете на копчето „Оди“. Во полето за дијалог што се појавува, кликнете на копчето „Избери...“. Во прозорецот „Изберете група ќелии“, штиклирајте го полето до „само видливи ќелии“. Кликнете на „OK“. Сега на истата избрана табела со десното глувче повикуваме контекстното мени. Кликнете на функцијата „Копирај“. На нова локација (во нашиот пример, ова е ќелијата A15), кликнете на „Вметни“. Сите. Испадна вака.
Како, без копирање, веднаш печатете податоци за филтер во ексел, видете ја статијата „Обележувач Ексел лист"Изглед на страница""
