Jeśli niektóre komórki, ciągi lub kolumny na arkuszu nie są wyświetlane, możesz skopiować wszystkie komórki (lub tylko widoczne komórki). Domyślnie kopie Excel nie tylko widoczne, ale także ukryte lub filtrowane komórki. Jeśli chcesz skopiować tylko widoczne komórki, wykonaj kroki opisane poniżej. Na przykład możesz skopiować tylko dane podsumowujące z arkusza strukturyzowanego.

Wykonaj poniższe kroki.

Uwaga: Podczas kopiowania wartości są konsekwentnie wkładane do ciągów i kolumn. Jeśli obszar wstawiania zawiera ukryte struny lub kolumny, może być konieczne wyświetlanie ich, aby zobaczyć wszystkie skopiowane dane.

Podczas kopiowania i wkładania widocznych komórek w zakresie danych, który zawiera ukryte komórki lub do którego zastosowano filtr, można zauważyć, że ukryte komórki są wkładane wraz z widoczną. Niestety, nie można zmienić tego parametru podczas kopiowania i wkładania zakresu komórek w programie Excel w Internecie, ponieważ wstawienie tylko widocznych komórek nie jest dostępny.

Jeśli jednak sformatujesz dane jako tabelę i zastosujesz filtr, możesz skopiować i wkleić tylko widoczne komórki.

Jeśli nie musisz sformatować danych jako tabeli, a aplikacja Classic Excel jest zainstalowana, można otworzyć książkę w nim, aby skopiować i wkleić widoczne komórki. Aby to zrobić, kliknij Otwarty w Excel. I postępuj zgodnie ze krokami opisanymi w artykule Kopiowania i włóż tylko widoczne komórki.

Dodatkowe informacje

Zawsze możesz zadać pytanie przez Excel Tech Community Specialist, poprosić o pomoc w społeczności społecznościowej odpowiedzi, a także oferta nowa cecha lub poprawa na stronie internetowej

Formatowanie warunkowe (5)
Listy i zakresy (5)
Makra (procedury VBA) (63)
Różne (39)
Excel Bugs and Glitches (4)

Jak wstawić skopiowane komórki tylko w widocznych / filtrowanych komórkach

Ogólnie rzecz biorąc, rozumiem, że znaczenie tego artykułu jest jasne z imienia. Po prostu rozszerzaj się.

Nie jest tajemnicą, że Excel pozwala podświetlić tylko widoczne linie. (na przykład, jeśli niektóre z nich są ukryte lub filtr).

Więc jeśli kopiujesz tylko widoczne komórki, są skopiowane, ponieważ powinny być kopiowane. Ale kiedy próbujesz wstawić skopiowany w zakresie, filtrowane (lub zawierające linie ukryte) jest wynikiem wstawiania, nie będzie dokładnie tak, jak się spodziewałeś. Dane zostaną wstawione nawet w ukrytych liniach.

Skopiuj pojedynczy zakres komórek i wstaw tylko do widocznego
Dlatego dane są włożone tylko w widocznych komórek, takie makro można zastosować:

Opcja Wyraźna dim rcoprange jako zasięg "To makro kopiowanie danych Sub My_Copy () Jeśli selection.Count\u003e 1 Następnie Ustaw RCopirRange \u003d selection.Specialcells (Xlvisible) Else: Ustaw RCOPirange \u003d Activectel End If End Sub "To makro wkłada dane zaczynające się od wybranej komórki Sub My_paste () Jeśli RCOPirange jest niczym, a następnie wyjdź pod Subf RCOPirange.areas.Count\u003e 1, a następnie MSGBOX "Włożony zakres nie powinien zawierać więcej niż jednego obszaru!", vbcritical, "Nieprawidłowa zasięg": Wyjdź SubM Dim RCLL jako zasięg, li tak długo, le tak długo, LCount tak długi, ICOL jako całkowitą, ICalCulation jako Calteger Aplikacja .Columns.count li \u003d 0: LCOnnt \u003d 0: Le \u003d ICOL - 1 dla każdego RCLLANGRANGE.COLRANKS (ICOL) .Cells do IF ACTIVELELL.OFFSET (LI, LE) (LI, LE). CENTIREROW.Hidden \u003d false następnie rcel.copy ActiveCell.offset (LI, LE): LCOUNT \u003d LCOUNT + 1 END Jeśli LI \u003d LI + 1 LOOP, gdy LCOUNT\u003e \u003d RCLL.ROW - RCOPYRANGAR.CELLS (1 ) .Row Następny RCell Następny ICOL Application.ScreenupDating \u003d true: Application.CalCulation \u003d ICCulation End Sub

Opcja Wyraźna dim RCOPirange AS RANGE "To makro kopiowanie podrzędne My_Copy (), jeśli selection.Count\u003e 1 Następnie Ustaw RCopirrange \u003d Wybór Wybrane komórki Sub My_Paste () Jeśli RCOPirange jest niczym, a następnie wyjście SubF IF RCOPirange.areas.Count\u003e 1 Następnie MSGBOX "Wkładany zakres nie powinien zawierać więcej niż jednego obszaru!", VBCRITYKI, "Nieprawidłowa zasięg": Wyjście Sub Dim RCell jako zasięg , Li tak długo, le tak długo, LCount tak długo, ICOL jako całkowitą, ICalCulation jako Calteger Application.ScreenUpdating \u003d False ICalCulation \u003d Application.CalCulation: Application.CalCulation \u003d -4135 dla ICOL \u003d 1 do rcopirange.Columns.count li \u003d 0 : LCOUNT \u003d 0: Le \u003d ICOL - 1 dla każdego RCLL w rcopirange.Columns (ICOL) .Cells do IF ACTICELELL.Offset (LI, LE). Inticrecolmn.hidden \u003d false i _ ACTSELL.Offset (LI, LE). Centirerow .Hidden \u003d false następnie rcel.copy ActiveCell.offset (LI, LE): LCOUNT \u003d LCOUNT + 1 koniec Jeśli L \u003d LI + 1 pętla podczas LCount\u003e \u003d rcell.Row - rcopirrange.Cells (1) .Row Następny RCell Następny ICOL Application.ScreenUpdating \u003d true: Application.CalCulation \u003d ICCulation End Sub

Aby uzyskać kompletność obrazu, dane makr są lepsze do przypisania klawisze skrótów (w poniższych kategoriach, odbywa się to automatycznie podczas otwierania książki z kodem). W tym celu poniższe kody muszą być po prostu skopiowane do modułu. Ta książka Thisworkbook) :

Opcja Wyraźna "Zapewniamy przypisanie klawiszy gorących przed zamknięciem książki prywatnej podsumpora_beforeclose (Anuluj jako Boolean) Aplikacja Workbook_open () Aplikacja.onkey "^ Q", "My_Copy": Application.onkey "^ W", "My_paste" End Sub

Teraz możesz skopiować żądany zakres, naciskając klawisze Ctrl. + p. i włóż go do filtrowanego - Ctrl. + w. .

Pobierz przykład

(46.5 KIB, 9,622 pobrań)

Skopiuj tylko widoczne komórki i wkładki tylko w widoczne
Na wniosek witryny odwiedzający zdecydowali się udoskonalić tej procedury. Teraz możliwe jest skopiowanie dowolnych zakresów: z ukrytymi rzędami, ukrytymi kolumnami i wkładaj skopiowane komórki również do dowolnych zakresów: z ukrytymi rzędami, ukrytymi kolumnami. Działa całkowicie jako poprzedni: naciskając klawisze Ctrl. + p. Skopiuj żądany zakres (z ukrytymi / filtrowanymi liniami i kolumnami lub nie ukrytych)i włóż kombinację klucza Ctrl. + w. . Wkładka jest wykonywana również w ukrytych / filtrowanych liniach i kolumnach lub bez ukrytych.
Jeśli formuły są obecne w zakresie kopiowania, aby uniknąć przesunięcia łącza, tylko wartości komórkowe można skopiować - tj. Podczas wstawiania wartości nie zostanie wstawionych formuł, ale wynik ich obliczeń. Lub jeśli chcesz zapisać formaty komórkowe, w których wystąpi wkładanie - tylko wartości komórki zostaną skopiowane i włożone. Aby to zrobić, wymień linię w kodzie (w poniższym pliku):

Rcell.copy rressell.offset (LR, LC)

rcell.copy rressell.offset (LR, LC)

do takiego:

Rressell.offset (LR, LC) \u003d RcelLeue

rressell.offset (LR, LC) \u003d RcelLeue

W poniższym pliku znajdują się obecne obie te linie, wystarczy pozostawić ten, który jest bardziej odpowiedni dla zadań.

Pobierz Przykład:

(54,5 KIB, 7,928 pobrań)

Tylko zobacz:
[]

Artykuł pomógł? Udostępnij link z przyjaciółmi! Lekcje wideo

("Dolny pasek" :( "Tekstyl": "statyczny", "TextPozycjonistyczny": "Dolny", "Textautohide": True, "TextPositionMarginstic": 0, "TextPositionDynstic": "Bottomleft", "TextPositionMarginalleft": 24, " TextpositionMarginightright ": 24," TextpositionMargintop ": 24," TextPositionMarginbottom ": 24," TextEffect ":" Slide "," TextEffecting ":" EaseoutCubic "," TextEffectionuration ": 600," TextEffersEfectionIciction ":" Left "," TextEffersIDLIDEDistance " : 30, "TextEffectdelay": 500, "TextEffersEgotate": False, "TextEffect1": "Slajd", "TextEffectlidirection1": "Prawa", "TextEffectslidistance1": 120, "TextEffecting11": "Uspokaja", "TextEffectdation1": 600 , "TextEffectDelay1": 1000, "TextEffect2": "Slajd", "TextEffectledEilection2": "Right", "TextEffersEffersIDedistance2": 120, "TextEffectle2": "Uspokój", "TextEffectdation2": 600, "TextEffectdelay2": 1500 "," TEXTCSSSSS ":" Wyświetlacz: blok; Wypełnienie: 12px; Tekst-Wyrównuj: W lewo; "," TextBGCSS ":" Wyświetlacz: blok; Pozycja: Absolute; Top: 0px; Lewo: 0px; Szerokość: 100%; Wysokość: 100% ; kolor - kolor: # 333333; nieprzezroczystość: 0,6; Filtr: a LPHA (Kiń \u003d 60); "," Titlecss ":" Wyświetlacz: blok; Pozycja: krewna; Czcionka: Bold 14px "Lucida Sans Unicode \\", "Lucida Grande", Sans-Serif, Arial; Kolor: #FFF; "," OpisCSS ":" Wyświetlacz: blok; Pozycja: krewna; Czcionka: 12px "Lucida Sans Unicode \\", "Lucida Grande", Sans-Serif, Arial; Kolor: #FFF; Margin-Top: 8px; "," Buttercss ":" Wyświetlacz: blok; Pozycja: krewna; Margin-Top: 8px; "," TextEffectresponsive ": true," TextEffecktResponsize ": 640," TitlecsSresponssive ":" Czcionka-Rozmiar: 12px; "," OpisSSresponssive ":" Wyświetlacz: Brak! Ważne; "," ButtoncssSresponssive ": "", "AddgoooOgonts": false, "Googlefonts": "", "TextleftrighterCorCorstic": 40))

Pavlov Nicholas.

W tym artykule chciałbym przedstawić Państwu najskuteczniejsze techniki pracy Microsoft Excel.Zebrałem mnie w ciągu ostatnich 10 lat pracy nad projektami i szkoleniami w tym wspaniałym programie. Nie ma opisu technologii Supertratu, ale są techniki dla każdego dnia - proste i wydajne, opisane bez "wody" - tylko "sucha pozostałość". Będziesz miał więcej niż jedną lub dwie minuty, aby opracować większość z tych przykładów, ale zaoszczędzą cię znacznie więcej.

Szybkie przejście do żądanego arkusza

Czy pracujesz z książkami Excel składającymi się z dużej liczby arkuszy? Jeśli jest więcej niż kilkanaście, wówczas każde przejście do następnej tego razu staje się niewielkim problemem. Proste i eleganckie rozwiązanie takiego zadania jest kliknięcie w lewym dolnym rogu okna na przyciskach przewijania arkuszy arkuszy nie pozostałych, ale prawy przycisk myszy - pojawi się spis treści pełna lista Wszystkie arkusze i na żądanym arkuszu można przenieść do jednego ruchu:

Jest znacznie szybszy niż przewijanie arkuszy arkuszy przez te same przyciski w poszukiwaniu żądanego jednego.

Kopiuj bez formatowania uszkodzeń

Ile setek (tysiące?) Kiedyś zobaczyłem to zdjęcie, stojąc za moimi słuchaczami podczas szkoleń: Użytkownik wchodzi do formuły w pierwszej komórce, a następnie "rozciąga" go w całej kolumnie, zakłócając formatowanie następujących linii, ponieważ to Metoda kopiuje nie tylko formułę, ale format komórki. Odpowiednio, musisz ręcznie poprawić uszkodzenia. Po drugie na kopiowaniu, a następnie 30 - Aby naprawić projekt uszkodzony przez kopiowanie.

Począwszy od programu Excel 2002, istnieje rozwiązanie tego problemu - proste i eleganckie. Natychmiast po kopiowaniu (przeciąganie) formuł do całej kolumny, musisz użyć tagu Smart - mała ikona, która tymczasowo pojawia się w prawym dolnym rogu zakresu. Naciśnięcie zostanie wyświetlona lista możliwych opcji kopiowania, w której można wybrać tylko wartości (formatowanie wypełnienia Wither). W tym przypadku formuły są kopiowane i formatowanie - Nie:

Kopiuj tylko widoczne komórki

Jeśli pracujesz w Microsoft Excel więcej niż w tygodniu, powinieneś już był miał podobny problem: w niektórych przypadkach, gdy kopiowanie wkładania komórek, są one włożone więcej niż na pierwszy rzut oka kopiowano. Może to wystąpić, jeśli zakres kopiowania obejmował ukryte struny / kolumny, grupowania, wyników pośrednich lub filtrowanie. Rozważmy jako przykład jeden z takich przypadków:

Wyniki pośrednie są liczone w tej tabeli i podejmuje grupowanie ciągów w miastach - łatwo jest zrozumieć przyciski "Plus-minus" po lewej stronie stołu i na luki w numeracji widzialnych linii. Jeśli wybierzesz, kopiuj i wklejaj dane z tej tabeli w zwykły sposób, otrzymamy 24 niepotrzebne struny. Chcemy również skopiować i wstawić tylko wyniki!

Możesz rozwiązać problem, starannie podkreślając każdy wiersz wyników i utrzymywanie klawisza CTRL - jak w przypadku wyboru zakresów nie-negatywnych. Ale jeśli nie ma trzech-pięć takich linii, ale kilkaset lub tysiące? Jest inny, szybszy i wygodny sposób:

Zaznacz kopiowany zakres (w naszym przykładzie jest A1: C29)

Naciśnij klawisz klawiatury F5, a następnie wybierz przycisk (Specjalny) w otwartym oknie.
Pojawi się okno, pozwalając użytkownikowi przydzielić wszystko z rzędu, ale tylko niezbędne komórki:

W tym oknie wybierz tylko tylko widoczne komórki (tylko widoczne komórki) i kliknij OK.

Uzyskany wybór można teraz bezpiecznie skopiować i włożyć. W rezultacie otrzymamy kopię widocznych komórek i wkładki zamiast niepotrzebnego 29 tylko 5 linii potrzebnych.

Jeśli istnieje podejrzenie, że taka operacja musisz często zrobić, ma sens, aby dodać przycisk do paska narzędzi Microsoft Excel, aby szybko połączyć taką funkcję. Można to zrobić za pośrednictwem Menu Narzędzia\u003e Konfiguracja (Narzędzia\u003e Dostosuj), a następnie przejdź do karty poleceń (Polecenia), w kategorii Edytuj (Edytuj) Znajdź przycisk Wybierz widoczne komórek i prześlij go do paska narzędzi:

Transformacja wierszy w kolumnach iz powrotem

Prosta obsługa, ale jeśli nie wiesz, jak to zrobić dobrze - możesz spędzić pół dnia na przeciąganie pojedynczych komórek ręcznie:

W rzeczywistości wszystko jest proste. W tej części. wyższa matematykaKtóry opisuje matryce, jest koncepcją transpozycji - działania, które zmienia struny i kolumny w matrycy w niektórych miejscach ze sobą. W programie Microsoft Excel jest on wdrażany w trzech ruchach: skopiuj tabelę

Kliknij prawym przyciskiem myszy pustą komórkę i wybierz polecenie specjalne (Paste Special)

W otwiera się okno, ustaw flagę do transpozycji (transpozycja) i kliknij OK:

Szybkie dodawanie danych do wykresu

Wyobraź sobie prostą sytuację: masz raport za ostatni miesiąc z schematem wizualnym. Zadanie - Dodaj nowe dane numeryczne do wykresu na ten miesiąc. Klasyczna ścieżka jest otwarta okna źródła danych dla diagramu, w którym dodajesz nową serię danych, wprowadzając jej nazwę i podświetli zakres żądanych danych. Ponadto jest często łatwiej powiedzieć niż do zrobienia - wszystko zależy od złożoności wykresu.

Inny sposób jest prosty, szybki i piękny - wybrać komórki z nowymi danymi, skopiuj je (Ctrl + C) i wklej (Ctrl + V) bezpośrednio na wykresie. Excel 2003, w przeciwieństwie do późniejszych wersji, nawet możliwość przeciągania dedykowanego zakresu komórek z danymi i rzucić go bezpośrednio do wykresu z myszą!

Jeśli chcesz kontrolować wszystkie niuanse i subtelności, możesz użyć niezwykłej i specjalnej wkładki, wybierając na edycji\u003e Paste Special (Edytuj\u003e Paste Special). W tym przypadku Microsoft Excel wyświetli okno dialogowe, które umożliwi konfigurację, gdzie i jak nowe dane zostaną dodane:

Podobnie możesz łatwo utworzyć diagram za pomocą danych z różnych tabel z różnych arkuszy. Aby wykonać to samo zadanie, zajmie dużo więcej czasu i siły.

Wypełnianie pustych komórek

Po rozładunku raportów z niektórych programów format Excel. Lub podczas tworzenia skonsolidowanych tabel użytkownicy często odbierają tabele z pustymi komórkami w niektórych kolumnach. Te przeskakiwanie nie pozwalają nam zastosować nas do tabel znanych i wygodnych narzędzi, takich jak autofiltr i sortowanie. Naturalnie istnieje potrzeba wypełnienia wartości pustek z wyższych komórek:

Oczywiście, z małą ilością danych, można go łatwo zrobić prosta kopiowanie - Ręcznie rozciągając każdą komórkę tytułową w kolumnie i w dół do pustych komórek. A jeśli jest kilkaset lub tysiące wierszy i kilkadziesiąt miast w stole?

Istnieje sposób szybkiego rozwiązania tego zadania za pomocą jednej formuły:

Zaznacz wszystkie komórki w kolumnie z pustkami (I.E. Zasięg A1: A12 w naszym przypadku)

Aby wybrać tylko puste komórki, naciśnij klawisz F5 iw oknie przejściowym, który zostanie otwarty, przycisk alokacji. Zobacz okno, które pozwala wybrać - które komórki chcemy przeznaczyć:

Ustaw przełącznik do pustej pozycji (puste) i kliknij OK. Teraz tylko puste komórki powinny pozostać w wyborze:

Bez zmieniających się alokacji, tj. Nie ma myszy dotykowej, wprowadzamy formułę w pierwszej dedykowanej komórce (A2). Kliknij na klawiaturę na znak "równy", a następnie w górę strzałki. Dostajemy formułę, która odnosi się do poprzedniej komórki:

Aby wejść do utworzonej formuły natychmiast do wszystkich wybranych pustych komórek, naciśnij klawisz Enter, ale kombinacja Ctrl + Enter. Formuła wypełnia wszystkie puste komórki:

Teraz pozostaje tylko w celu zastąpienia formuł na wartościach do ustalania wyników. Zaznacz zakres A1: A12, skopiuj go i włóż je do komórek za pomocą specjalnej wkładki.

Rozładować się

Recepcja, która bez przesady powinna znać każdego, kto pracuje w Excelu. Jego użycie jest w stanie poprawić, prawie dowolną tabelę, niezależnie od jego celu. W ogóle szkolenia próbuję pokazać go moim słuchaczom pierwszego dnia.

Pomysł jest bardzo prosty - we wszystkich przypadkach, gdy należy wprowadzić dane z dowolnego wybierania, zamiast ręcznie wprowadzać do komórki z klawiatury, wybierz żądaną myszy na liście rozwijanej:

Wybór towarów z cennika, nazwa klienta z bazy klienta, FIO pracownika z personelu i tak dalej. Opcje aplikacji dla tego zestawu funkcji.

Aby utworzyć listę rozwijaną w komórce:

Wybierz komórki, w których chcesz utworzyć listę rozwijaną.

Jeśli masz Excel 2003 lub więcej, wybierz DATA\u003e Walidacja w menu Walidacja Data\u003e. Jeśli masz Excel 2007/2010, przejdź do karty Data i kliknij Walidacja danych.

W otwartym oknie, wybierz opcję Lista (Lista) z listy rozwijanej.

W polu źródłowym (źródło) należy określić wartości, które powinny być wymienione. Tutaj możliwe są opcje:

Wpisz do tych opcji tekstu pola przez punkt przecinka

Jeśli zakres komórek o wartościach źródłowych znajduje się na bieżącym arkuszu - wystarczy po prostu podświetlić go z myszą.

Jeśli jest na innym arkuszu tej książki, będzie musiał podać nazwę z wyprzedzeniem (aby podkreślić komórki, naciśnij Ctrl + F3, wprowadź nazwę zakresu bez spacji), a następnie zarejestruj tę nazwę w polu

Wybór danych w programie Excel spędził za pomocą filtra lub sortowania. Teraz muszą być drukowane lub przenoszenie do innego miejsca. Kopiowanie do Excel. Jest skonfigurowany tak, że ukryte komórki są kopiowane.
Rozważ dwa sposoby do aK Kopiuj filtrowane wiersze w Excelu.
Pierwsza droga.
Jest wspaniała funkcja w Excelu - funkcja "specjalna wkładka" w programie Excel.
Mamy więc stół.
Jak zainstalować filtr, spójrz w artykule "Filtr w programie Excel. ".
Usuwamy filtr z listy wszystkich Ivanov. Okazało się tak.
Wybierz tabelę, kliknij menu kontekstowe "Kopiuj". Naciśnij lewy mysz w komórce A9, wybierz "Wartości" w menu kontekstowym.
Kliknij OK". Voila. Nie tylko wartość widzialnych linii, ale także format komórek.
Jest jeden niuans - Wstaw filtrowane dane nie znajduje się w ciągu, gdzie filtr jest. Na przykład w naszym przykładzie - nie w linii 1-7, ale poniżej lub na innym arkuszu itp. Jeśli włożysz do łańcucha, w którym wartość filtra jest warte, następnie włożone są filtrowane dane i linie ukryte przez filtr. Ogólnie okaże się owsianka. Drugi sposób.
Tabela jest taka sama. Wybierz tabelę z filtrowanym danymi. Na karcie Home kliknij przycisk Edytuj znajdź i podświetl funkcje w programie Excel. Następnie naciśnij przycisk "Idź". W wyświetlonym oknie dialogowym naciśnij przycisk "Wybierz ...". W oknie "Wybierając grupę grupy" umieścimy kleszcz na ciąg "tylko widoczne komórki". Kliknij OK". Teraz na tej samej przydzielonej tabeli o prawej stronie myszy menu kontekstowe. Naciśnij funkcję "Kopiuj". W nowym miejscu (w naszym przykładzie jest to komórka A15) kliknij "Wklej". Wszystko. Okazało się tak.
Jak, nie kopiowanie, natychmiast drukuj dane filtra w programie Excel, Spójrz na artykuł "Bookmark Leaf Excel" Znakowanie stron ""