Korzystając z Arkuszy kalkulacyjnych Google, możesz pisać zapytania . Jeśli jednak masz litery w cudzysłowie, nie są one aktualizowane w miarę zmiany kolejności kolumn.

Czy istnieje sposób na napisanie tych zapytań, aby nie musiały być aktualizowane przy każdym dodawaniu lub usuwaniu kolumny?

Czy można użyć nazwanych zakresów w zapytaniach, aby rozwiązać ten problem?

Oto przykład: jeśli dodasz kolumnę po „F”, wówczas kolumna „G” zostanie wypchnięta do „H” i zmieni się znaczenie formuły.

= Zapytanie (B: J, „wybierz średnią grupę (J) według G”)

Powiązane pytania

To pytanie nie jest tożsame z używaniem zapytania z nagłówkami kolumn zamiast liter kolumn, ponieważ to pytanie koncentruje się na użyciu nazwanych zakresów.

Jest to dość trudne, ale jest możliwe dzięki Zasięgowi Pomocnika i pewnej konkatenacji.

Co musi być zrobione:

1. Utwórz nazwany zakres COLS, aby przenosić litery kolumn w następujący sposób:

   A  
   B  
   C  
   D  
   E  
   ...
   

   Zrób to w pionie, jak pokazano.

2. Złóż ciąg zapytania w następujący sposób:

   =QUERY( B:J, "SELECT AVG(" & INDEX(**COLS**, COLUMN(J1)) & ") GROUP BY " & INDEX(**COLS**, COLUMN(G1)) )
   

Czy istnieje sposób na napisanie tych zapytań, aby nie musiały być aktualizowane przy każdym dodawaniu lub usuwaniu kolumny?

Czy można użyć nazwanych zakresów w zapytaniach, aby rozwiązać ten problem?

Tak, możesz użyć nazwanych zakresów zamiast wbudowanych nagłówków kolumn, ale podstawowa idea jest taka sama, jak inne odpowiedzi na to pytanie, sugerowane w innych podobnych pytaniach w tej witrynie: arkusz kalkulacyjny powinien być ustawiony tak, aby budował dynamiczny ciąg SQL .

Powiedzmy, że Ji Godpowiada nazwanych zakresów Gradesi Classodpowiednio.

CELL("address",Grades)wróci "$J:$J". Aby uzyskać odwołanie MID(CELL("address",Grades),2,1)do kolumny, użyj get "J". Ostateczna formuła to

= QUERY (B: J, „WYBIERZ AVG (” i
MID (CELL („adres”, stopnie), 2,1) i
") GRUPUJ WEDŁUG "&
MID (CELL („adres”, klasa), 2,1))

COLUMN(Grades)zwróci liczbę całkowitą (liczba w postaci 1,2,3 itd.) Aby uprościć formułę, aby utworzyć argument instrukcji SQL, sugeruję wymusić na SQL odwoływanie się do kolumn w postaci COL1, COL2 itp. za pomocą tablicy notacja ( {B:J}). Ostateczna formuła to

= QUERY ({B: J}, „WYBIERZ AVG (Kolumna” i KOLUMNA (stopnie) -1 i „) GRUPA WG Kolumny” i KOLUMNA (Klasa) -1)

Arkusz demonstracyjny

Bibliografia

Korzystanie z tablic w Arkuszach Google - Edytory Dokumentów Google - Pomoc

Odpowiedź Rubena nie rozwiązuje problemu we wszystkich przypadkach, ponieważ MID(CELL("address",Grades),2,1)kończy się niepowodzeniem, jeśli Gradeszakres zostanie przeniesiony do kolumny z dwiema literami $AA:$AA.

W takim przypadku MID(CELL("address",Grades),2,2)potrzebna jest formuła . W przypadku nazwanych zakresów, które obejmują lub przylegają do $Z:$AAzakresu, ta formuła staje się problematyczna.

Formuła REGEXEXTRACT( CELL("address", Grades), "\$([a-zA-Z]+)\$")rozwiązuje problem w ogólnym przypadku, ale mocno kwestionuję jego wydajność procesora, gdy jest używana w całym dużym arkuszu kalkulacyjnym.

Natrafiłem na tę stronę infoinspired.com, która wyjaśnia jeden ze sposobów. Przetestowałem to dokładnie tak, jak w przykładzie, który podał i zadziałało. Ustawić:

      A        B        F                                     G
 1  names     Age   =left(address(row(B1),column(B1),4))   =QUERY(A:B,"Select "&Age)
 2  joey      32
 3  nicole    26
 4  sally     66

Wyjaśnienie F1: Formuła ADRES, która zawiera funkcje ROW i COLUMN, zwraca adres komórki B1. Funkcja LEWA wyodrębnia z niej pierwszą literę, która jest nagłówkiem kolumny.

--- Kliknij komórkę F1, a następnie przejdź do menu Dane> Nazwane zakresy i nazwij zakres „Wiek” (bez cudzysłowu)

Teraz możesz sformułować swoje zapytanie jako: =QUERY(A:B,"Select "&Age)

Na swojej stronie ma bardziej skomplikowane przykłady, które zamierzam teraz przetestować, ale jestem zachęcany, że pierwszy przykład zadziałał!

Edytuj - przepraszam, jeśli dokładnie to zaproponowali inni, udzielając odpowiedzi. Właśnie znalazłem ten przykład krystalicznie czysty i byłem w stanie go rozgryźć. Pomyślałem, że może pomóc innym z tym samym pytaniem.