Jak używać formuł tablicowych w Arkuszach Google

Opublikowany: 2023-06-10

Na początku 2023 roku Google wprowadził kilka nowych funkcji do Arkuszy, w tym osiem do pracy z tablicami. Za pomocą tych funkcji można przekształcić tablicę w wiersz lub kolumnę, utworzyć nową tablicę z wiersza lub kolumny albo dołączyć bieżącą tablicę.

Mając większą elastyczność w pracy z tablicami i wykraczając poza podstawową funkcję FORMUŁA TABLIC, przyjrzyjmy się, jak używać tych funkcji tablicowych z formułami w Arkuszach Google.

Spis treści

    Porada : Niektóre z tych funkcji mogą wyglądać znajomo, jeśli korzystasz również z programu Microsoft Excel.

    Przekształć tablicę: TOROW i TOCOL

    Jeśli masz tablicę w zbiorze danych, którą chcesz przekształcić w pojedynczy wiersz lub kolumnę, możesz użyć funkcji TOROW i TOCOL.

    Składnia każdej funkcji jest taka sama, TOROW(tablica, ignorowanie, skanowanie) i TOCOL(tablica, ignorowanie, skanowanie) , gdzie dla obu wymagany jest tylko pierwszy argument.

    • Tablica : tablica, którą chcesz przekształcić, sformatowana jako „A1:D4”.
    • Ignoruj ​​: Domyślnie żaden parametr nie jest ignorowany (0), ale można użyć wartości 1, aby zignorować spacje, 2, aby zignorować błędy, lub 3, aby zignorować spacje i błędy.
    • Skanuj : Ten argument określa sposób odczytywania wartości w tablicy. Domyślnie funkcja skanuje według wierszy lub przy użyciu wartości False, ale jeśli wolisz, możesz użyć opcji True, aby skanować według kolumn.

    Przyjrzyjmy się kilku przykładom z wykorzystaniem funkcji TOROW i TOCOL oraz ich formuł.

    W tym pierwszym przykładzie weźmiemy naszą tablicę od A1 do C3 i zamienimy ją w wiersz, używając domyślnych argumentów z następującą formułą:

    =TOROW(A1:C3)

    Jak widać, tablica jest teraz w rzędzie. Ponieważ użyliśmy domyślnego argumentu skanowania , funkcja czyta od lewej do prawej (A, D, G), w dół, a następnie ponownie od lewej do prawej (B, E, H), aż do zakończenia — skanowanie po wierszu.

    Aby odczytać tablicę według kolumny zamiast wiersza, możemy użyć True jako argumentu scan . Argument ignorowania pozostawimy pusty. Oto formuła:

    =TOROW(A1:C3;;PRAWDA)

    Teraz widzisz, że funkcja odczytuje tablicę od góry do dołu (A, B, C), od góry do dołu (D, E, F) i od góry do dołu (G, H, I).

    Funkcja TOCOL działa w ten sam sposób, ale przekształca tablicę w kolumnę. Korzystając z tego samego zakresu, od A1 do C3, oto formuła z domyślnymi argumentami:

    =TOKOL(A1:C3)

    Ponownie, używając domyślnego argumentu scan , funkcja czyta od lewej do prawej i podaje wynik jako taki.

    Aby odczytać tablicę według kolumny zamiast wiersza, wstaw True dla argumentu scan w następujący sposób:

    =TOKOL(A1:C3;;PRAWDA)

    Teraz widzisz, że funkcja odczytuje tablicę od góry do dołu.

    Utwórz nową tablicę z wierszy lub kolumn: WYBIERZ i WYBIERZ

    Możesz chcieć utworzyć nową tablicę z istniejącej. Pozwala to utworzyć nowy zakres komórek zawierający tylko określone wartości z innego zakresu. W tym celu użyjesz funkcji Arkuszy Google WYBIERZ i WYBIERZ.

    Składnia każdej funkcji jest podobna, WYBIERZ (tablica, numer_wiersza, numer_wiersza_opcja) i WYBIERZECOLS (tablica, numer_kolumny, numer_opcji_kolumny), gdzie pierwsze dwa argumenty są wymagane dla obu.

    • Tablica : istniejąca tablica sformatowana jako „A1:D4”.
    • Row_num lub Col_num : Numer pierwszego wiersza lub kolumny, którą chcesz zwrócić.
    • Row_num_opt lub Col_num_opt : Liczby dodatkowych wierszy lub kolumn, które chcesz zwrócić. Google sugeruje użycie liczb ujemnych do zwracania wierszy od dołu do góry lub kolumn od prawej do lewej.

    Przyjrzyjmy się kilku przykładom wykorzystującym WYBIERZ i WYBIERZEKOLE oraz ich formuły.

    W tym pierwszym przykładzie użyjemy tablicy od A1 do B6. Chcemy zwrócić wartości w wierszach 1, 2 i 6. Oto formuła:

    =WIERSZY WYBIERANIA(A1:B6;1;2;6)

    Jak widać, otrzymaliśmy te trzy wiersze, aby utworzyć naszą nową tablicę.

    W innym przykładzie użyjemy tej samej tablicy. Tym razem chcemy zwrócić rzędy 1, 2 i 6, ale z 2 i 6 w odwrotnej kolejności. Możesz użyć liczb dodatnich lub ujemnych, aby otrzymać ten sam wynik.

    Używając liczb ujemnych, użyjesz tego wzoru:

    =WIERSZY WYBIERANIA(A1:B6;1;-1;-5)

    Aby wyjaśnić, 1 to pierwszy wiersz do zwrócenia, -1 to drugi wiersz do zwrócenia, czyli pierwszy wiersz zaczynający się od dołu, a -5 to piąty wiersz od dołu.

    Używając liczb dodatnich, użyjesz tego wzoru, aby uzyskać ten sam wynik:

    =WIERSZY WYBIERANIA(A1:B6;1;6;2)

    Funkcja WYBIERZEKONOMY działa podobnie, z tą różnicą, że używasz jej, gdy chcesz utworzyć nową tablicę z kolumn zamiast z wierszy.

    Korzystając z tablicy od A1 do D6, możemy zwrócić kolumny 1 (kolumna A) i 4 (kolumna D) za pomocą następującej formuły:

    =WYBIERZKOLE(A1:D6;1;4)

    Teraz mamy naszą nową tablicę zawierającą tylko te dwie kolumny.

    Jako inny przykład użyjemy tej samej tablicy, zaczynając od kolumny 4. Następnie dodamy najpierw kolumny 1 i 2 z 2 (kolumna B). Możesz użyć liczb dodatnich lub ujemnych:

    =WYBIERZKOLE(A1:D6;4;2;1)

    =WYBIERZKOLE(A1:D6;4;-3;-4)

    Jak widać na powyższym zrzucie ekranu, z formułami w komórkach, a nie na pasku formuły, otrzymujemy ten sam wynik przy użyciu obu opcji.

    Uwaga : ponieważ Google sugeruje użycie liczb ujemnych w celu odwrócenia kolejności wyników, pamiętaj o tym, jeśli nie otrzymujesz prawidłowych wyników z liczbami dodatnimi.

    Zawiń, aby utworzyć nową tablicę: WRAPROWS i WRAPCOLS

    Jeśli chcesz utworzyć nową tablicę z istniejącej, ale opakować kolumny lub wiersze określoną liczbą wartości w każdym, możesz użyć funkcji WRAPROWS i WRAPCOLS.

    Składnia każdej funkcji jest taka sama, WRAPROWS (zakres, liczba, dopełnienie) i WRAPCOLS (zakres, liczba, dopełnienie), gdzie pierwsze dwa argumenty są wymagane dla obu.

    • Zakres : istniejący zakres komórek, którego chcesz użyć jako tablicy, sformatowany jako „A1:D4”.
    • Liczba : Liczba komórek w każdym wierszu lub kolumnie.
    • Pad : Możesz użyć tego argumentu, aby umieścić tekst lub pojedynczą wartość w pustych komórkach. Spowoduje to zastąpienie błędu #N/D, który pojawi się w przypadku pustych komórek. Umieść tekst lub wartość w cudzysłowie.

    Przyjrzyjmy się kilku przykładom z wykorzystaniem funkcji WRAPROWS i WRAPCOLS oraz ich formuł.

    W tym pierwszym przykładzie użyjemy zakresu komórek od A1 do E1. Stworzymy nową tablicę zawijającą wiersze z trzema wartościami w każdym wierszu. Oto formuła:

    =ZAWIJANIE PROW(A1:E1;3)

    Jak widać, mamy nową tablicę z poprawnym wynikiem, po trzy wartości w każdym wierszu. Ponieważ w tablicy mamy pustą komórkę, wyświetlany jest błąd #N/D. W następnym przykładzie użyjemy argumentu pad , aby zastąpić błąd tekstem „Brak”. Oto formuła:

    =WRAPROWS(A1:E1;3;”Brak”)

    Teraz możemy zobaczyć słowo zamiast błędu Arkuszy Google.

    Funkcja WRAPCOLS robi to samo, tworząc nową tablicę z istniejącego zakresu komórek, ale robi to przez zawijanie kolumn zamiast wierszy.

    Tutaj użyjemy tej samej tablicy, od A1 do E3, zawijając kolumny trzema wartościami w każdej kolumnie:

    =WRAPCOLS(A1:E1;3)

    Podobnie jak w przykładzie WRAPROWS, otrzymujemy poprawny wynik, ale również błąd z powodu pustej komórki. Za pomocą tej formuły możesz użyć argumentu pad , aby dodać słowo „Pusty”:

    =WRAPCOLS(A1:E1;3;Pusty”)

    Ta nowa tablica wygląda znacznie lepiej ze słowem zamiast błędu.

    Połącz, aby utworzyć nową tablicę: HSTACK i VSTACK

    Dwie ostatnie funkcje, którym się przyjrzymy, służą do dołączania tablic. Dzięki HSTACK i VSTACK możesz dodać dwa lub więcej zakresów komórek, aby utworzyć pojedynczą tablicę, poziomo lub pionowo.

    Składnia każdej funkcji jest taka sama, HSTACK (zakres1, zakres2,…) i VSTACK (zakres1, zakres2,…), gdzie wymagany jest tylko pierwszy argument. Jednak prawie zawsze będziesz używać drugiego argumentu, który łączy inny zakres z pierwszym.

    • Zakres1 : Pierwszy zakres komórek, którego chcesz użyć w tablicy, sformatowany jako „A1:D4”.
    • Zakres2,… : Drugi zakres komórek, który chcesz dodać do pierwszego w celu utworzenia tablicy. Możesz połączyć więcej niż dwa zakresy komórek.

    Spójrzmy na kilka przykładów użycia HSTACK i VSTACK oraz ich formuł.

    W tym pierwszym przykładzie połączymy zakresy od A1 do D2 z zakresami od A3 do D4 za pomocą następującego wzoru:

    =HSTACK(A1:D2;A3:D4)

    Możesz zobaczyć nasze zakresy danych połączone w jedną tablicę poziomą.

    Dla przykładu funkcji VSTACK łączymy trzy zakresy. Korzystając z poniższego wzoru, użyjemy zakresów od A2 do C4, od A6 do C8 i od A10 do C12:

    = VSTACK(A2:C4;A6:C8;A10:C12)

    Teraz mamy jedną tablicę ze wszystkimi naszymi danymi przy użyciu formuły w jednej komórce.

    Z łatwością manipuluj tablicami

    Chociaż w pewnych sytuacjach możesz używać FORMUŁY TABLICOWEJ, na przykład w przypadku funkcji SUMA lub JEŻELI, te dodatkowe formuły tablicowe Arkuszy Google mogą zaoszczędzić czas. Pomagają uporządkować arkusz dokładnie tak, jak chcesz, za pomocą jednej formuły tablicowej.

    Aby uzyskać więcej podobnych samouczków, ale z funkcjami innymi niż tablicowe, zobacz, jak używać funkcji LICZ.JEŻELI lub SUMA.JEŻELI w Arkuszach Google.