Zastosuj formułę do całej kolumny


99

Próbuję przekodować cały kod pocztowy z kolumny A do kolumny B za pomocą wzoru:

=TEXT(A1,"00000")

Lubię to:

wprowadź opis obrazu tutaj

Chcę, aby każda komórka w kolumnie B została przekształcona przy użyciu powyższego wzoru. Ale mam ponad 40 000 wierszy, więc nie można przeciągnąć formuły w dół i zastosować jej do całej kolumny B.

Czy istnieje formuła, której mógłbym użyć do rozwiązania tego problemu?

Odpowiedzi:


92

Myślę, że to nowsza funkcja, ale działa dla mnie:

Dwukrotne kliknięcie kwadratu w prawym dolnym rogu podświetlonej komórki powoduje skopiowanie formuły podświetlonej komórki.

Mam nadzieję, że to pomoże.


20
To jest prawidłowa odpowiedź na pytanie. Dwukrotne kliknięcie „wskaźnika przeciągnięcia” (prawy dolny róg) spowoduje skopiowanie formuły do ​​wszystkich komórek. Upewnij się, że najpierw Wyczyść dane kolumny i zastosuj formułę tylko do jednej. Powyższa odpowiedź z „ArrayFormula” wydaje się być w stanie zastosować wiele komórek do jednej formuły, czego nie oczekiwał OP.
Thanasis Kapelonis

3
Dzięki za udostępnienie. Dla mnie działa dobrze, ale wolę używać funkcji ArrayFormula, po prostu dlatego, że w przeciwieństwie do Twojej metody, która w zasadzie kopiowałaby dokładnie tę samą formułę do każdej komórki, ArrayFormula będzie bardziej dynamiczna i można ją łatwiej zaprogramować.
Shahab Yarmohammadi

3
@ThanasisKapelonis, nie musisz czyścić danych, wystarczy dwukrotnie kliknąć wskaźnik przeciągania i zastosować formułę do wszystkich kolejnych wierszy, w których dane w każdym symbolu zastępczym są dostępne w odpowiednich kolumnach dla tego wiersza. Zatrzyma się tam, gdzie brakuje danych. jak pokazał Brett Sutton
dresh

2
Mam za dużo danych, aby odpowiedzieć na to pytanie, nie udaje się i muszę ponownie załadować stronę.
Daniel Ryan

1
Chociaż może to być cenna wskazówka, aby rozwiązać problem, dobra odpowiedź również pokazuje rozwiązanie. Proszę edit dostarczyć przykładowy kod, aby pokazać to, co masz na myśli. Alternatywnie, zamiast tego rozważ napisanie tego jako komentarza.
Toby Speight,

58

Myślę, że masz szczęście. Spróbuj wpisać w B1:

=text(A1:A,"00000")

(bardzo podobnie!), ale przed uderzeniem Enternaciśnij Ctrl+ Shift+ Enter.


33
Kiedy próbuję Ctrl + Shift + Enter, po prostu otacza moją formułę ArrayFormula(…).
binki

2
@pnuts co więc masz na myśli, że powinno? to też nie działa dla mnie. nadal otrzymuj ArrayFormula (
JayPex,

4
@pnuts może to się zmieniło w zeszłym roku, ale ArrayFormula nie rozszerza formuły w dół kolumny B jako obraz z OP. U mnie po prostu wypełnia wartość wybranej komórki.
Assimilater

12
Dzięki za udostępnienie. U mnie zadziałało naprawdę dobrze. Myślę, że jedna rzecz, której ludziom może brakować, to część B1: B, z którą walczyłem ze sobą przez kilka sekund. Musisz zamienić swoją pojedynczą kolumnę, powiedzmy B1, tablicą kolumn B1: B w swojej formule, aby działała. Jak dotąd jest to najlepsza metoda i najbardziej dynamiczna.
Shahab Yarmohammadi

4
To zadziałało również dla mnie. Dzięki za wyjaśnienia w komentarzach. Dodatkową rzeczą, która mnie zaskoczyła, było to, że wszystkie komórki poniżej muszą być puste. Myślę, że myślałem, że zostaną po prostu nadpisane.
LOAS

56

Wygląda na to, że niektóre inne odpowiedzi stały się nieaktualne, ale dla mnie zadziałało:

  1. Kliknij komórkę z tekstem / formułą do skopiowania
  2. Shift + kliknij ostatnią komórkę, do której chcesz skopiować
  3. Ctrl + Enter

(Zwróć uwagę, że zastępuje to tekst, jeśli komórki docelowe nie są puste)


8
Działa z CMD + Enter na Macu.
StapleGun

4
To najlepsza odpowiedź w 2019 r.
Happy Bird

1
Ta odpowiedź działa dla znacznie szerszego zakresu formuł i powinna być akceptowaną odpowiedzią
phlare

1
Działa tylko na jednej kolumnie na raz, ale działa dobrze. Jeśli chcesz zrobić to szybko dla wielu rzeczy, Ctrl+Shift+Downwybierzesz całą drogę do dołu bieżącej kolumny, Ctrl+Entertak jak powiedziałeś, a następnie Up, Right, [Down](ostatnia nie będzie potrzebna, jeśli jesteś w wierszu 1), aby przejść do następna kolumna.
Adam Barnes

Działa jak urok w 2020 roku. Dzięki!
nxmohamad

9

Powiedzmy, że chcesz zamienić coś w tablicy ciągów i nie chcesz wykonywać kopiowania i wklejania na całym arkuszu .

Weźmy to jako przykład:

  • Tablica ciągów w kolumnie „A”: {jabłko, banan, pomarańcza, ..., awokado}
  • Chcesz zamienić znak „a” na „x”, aby uzyskać: {xpple, bxnxnx, orxnge, ..., xvocado}

Aby zastosować tę formułę do całej kolumny (tablicy) w czysty i elegancki sposób, możesz wykonać:

=ARRAYFORMULA(SUBSTITUE(A:A, "a", "x"))

Działa również dla tablic 2D, powiedzmy:

=ARRAYFORMULA(SUBSTITUE(A2:D83, "a", "x"))

8

Znalazłem inne rozwiązanie:

  • Zastosuj formułę do pierwszych 3 lub 4 komórek kolumny
  • Ctrl+ Cformuła w jednym z ostatnich wierszy (jeśli skopiujesz pierwszą linię, nie zadziała)
  • Kliknij nagłówek kolumny, aby zaznaczyć całą kolumnę
  • Naciśnij Ctrl+, Vaby wkleić go we wszystkich komórkach poniżej

2
Wydaje się, że działa to bardziej niezawodnie niż dwukrotne kliknięcie. Dzięki!
Ivan

5

To jest dla tych, którzy chcą szybko nadpisać komórki kolumn (bez wycinania i kopiowania). Działa to tak samo, jak dwukrotne kliknięcie pola komórki, ale w przeciwieństwie do dwukrotnego kliknięcia nadal działa po pierwszej próbie.

  1. Wybierz komórkę kolumny , którą chcesz skopiować w dół
  2. Naciśnij Ctrl+ Shift+, aby zaznaczyć komórki poniżej
  3. Naciśnij Ctrl+, Enteraby skopiować zawartość pierwszej komórki do komórek poniżej

PREMIA:

Skrót do przejścia do najniższej zawartości (w celu podwójnego sprawdzenia kopii) to Ctrl+ . Aby wrócić do góry, możesz użyć Ctrl+, ale jeśli górne wiersze są zamrożone, musisz również nacisnąć Enterkilka razy.


1
To najprostszy sposób. Dzięki!
Sopia Alfred

0

Aby było jasne, kiedy użyjemy wskaźnika przeciągania, skopiuje on tylko wartości komórek w dół kolumny, podczas gdy wartość znajduje się w sąsiedniej komórce w danym wierszu. Gdy tylko operacja przeciągania zobaczy sąsiednią komórkę, która jest pusta, przestanie kopiować formułę.

.na przykład

1,a,b
2,a
3,
4,a

Jeśli powyżej jest arkuszem kalkulacyjnym, użycie wskaźnika podwójnego kliknięcia w komórce „b” wypełni wiersz 2, ale nie trzeci lub czwarty.


1
Jeśli próbujesz zastosować formułę do wierszy w przefiltrowanym zestawie w programie Excel za pomocą „Dwukrotnego kliknięcia wskaźnika przeciągania”, zostanie ona zatrzymana za każdym razem, gdy skacze numer seryjny wiersza, tj. Wiersz nr. 1, 2, 3, jeśli pojawią się w kolejności, zostaną zastosowane do formuły, jednak jeśli wiersz nr. 5 pojawia się po rzędzie nr. 3 (Ze względu na filtrowanie), a następnie wiersz nr. 5 nie spowoduje zastosowania formuły. W takim przypadku musisz skopiować z komórki w wierszu nr. 3 i wklej go w wierszu nr. 5, a następnie użyj tego samego podwójnego kliknięcia. Tak, excel Sucks.
dresh

Chociaż może to być cenna wskazówka, aby rozwiązać problem, dobra odpowiedź również pokazuje rozwiązanie. Proszę edit dostarczyć przykładowy kod, aby pokazać to, co masz na myśli. Alternatywnie, zamiast tego rozważ napisanie tego jako komentarza.
Toby Speight,

0

Możesz użyć Ctrl + Shift + Down + D, aby dodać formułę również do każdej komórki w kolumnie.

Po prostu kliknij / zaznacz komórkę równaniem / formułą, którą chcesz skopiować, a następnie przytrzymaj Ctrl + Shift + Down + D, a twoja formuła zostanie dodana do każdej komórki.


0

Żeby nie stracić odpowiedzi, która działa:

  1. Wybierz komórkę do skopiowania
  2. Wybierz ostatnią komórkę w kolumnie
  3. Naciśnij klawisze CTRL + D

-2

Możesz wypełnić kolumnę, klikając dwukrotnie w prawym dolnym rogu komórki, z której chcesz skopiować (punkt na polu, który w przeciwnym razie byś przeciągnął) i zostanie zastosowany do całej kolumny.

Uwaga: to nie działa, jeśli masz zastosowany filtr, ani jeśli coś już jest w komórkach poniżej.


Jest to poprawne, jednak musisz się upewnić, że żadne dane nie znajdują się w komórkach pod komórką zawierającą formułę.
BinaryJoe01,

Chociaż może to być cenna wskazówka, aby rozwiązać problem, dobra odpowiedź również pokazuje rozwiązanie. Proszę edit dostarczyć przykładowy kod, aby pokazać to, co masz na myśli. Alternatywnie, zamiast tego rozważ napisanie tego jako komentarza.
Toby Speight,
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.