Czy mogę używać nagłówków kolumn w = QUERY?

Po przeczytaniu dokumentacji dla =QUERYfunkcji ^{1 , 2 , 3} niektóre z nich wydają się sugerować, że powinienem być w stanie używać nagłówków kolumn bezpośrednio w moim zapytaniu. Na przykład, =QUERYfunkcja przyjmuje trzeci opcjonalny parametr , HEADERSktóry pozwala określić liczbę wierszy nagłówka,.

Większość moich zapytań byłaby o wiele ładniejsza, gdybym mógł użyć nagłówków kolumn, a zatem nie musiałbym używać indeksów kolumn, ale nie jestem w stanie sprawić, by działało.

Przykład:

A      B         C
---------------------
Name   Phone     City
Vidar  12345678  Oslo
Rupert 32165487  Berlin

Jestem w stanie zapytać o to za pomocą indeksów kolumn:

=QUERY(Sheet1!A1:C; "select A, B, C where A = 'Vidar'"; 1)

... ale nie używając nagłówków kolumn:

=QUERY(Sheet1!A1:C; "select A, B, C where Name = 'Vidar'"; 1)

... to daje mi błąd: nieprawidłowe zapytanie: kolumna [nazwa] nie istnieje w tabeli

Czy w ogóle można używać takich nagłówków kolumn? Jeśli nie, jaki jest cel tego HÈADERSparametru?

Trzeci parametr, o którym mówisz w QUERYfunkcji, służy do sterowania nagłówkami. Jeśli tak -1, arkusz kalkulacyjny Google przedstawi własny wybór przy wybieraniu nagłówków na podstawie dostępnych danych:

Użyłem następującego zestawu danych:

Jeśli ustawione na 0, to nagłówek nie będzie używany, pozostawiając:

Jeśli ustawione na 1, zostanie użyty pierwszy wiersz, pozostawiając:

Jeśli ustawione na 2lub -1lub left blank, zostaną użyte pierwsze dwa rzędy, pozostawiając:

Używanie nagłówków, tak jak w przykładzie, nie jest możliwe. Najbliższą rzeczą byłoby użycie QUERYfunkcji opisanej w tej odpowiedzi. Tam używana jest nazwa quasi-kolumny.

Czy w ogóle można używać takich nagłówków kolumn?

Tak to mozliwe. Najpierw musisz użyć PODAJNIK, aby uzyskać numer kolumny, której wartość odpowiada „Nazwa”. Następnie musisz użyć ADRESU, aby uzyskać odwołanie do komórki. Na koniec musisz użyć SUBSTITUTE, aby usunąć numer wiersza z odwołania do komórki.

=QUERY(Sheet1!A1:C,"SELECT A, B, C where "&SUBSTITUTE(ADDRESS(1,MATCH("Name",Sheet1!A1:C1,0),4),1,"")&" = 'Vidar'")

• Przykład w Arkuszach Google
• Szczegółowe objaśnienie wzoru

Będąc starym postem, chciałem dodać moje rozwiązanie do miksu. Możesz użyć nazw kolumn, które uważam za bardziej przydatne. W ten sposób nie musisz edytować wywołań funkcji zapytania podczas wstawiania lub usuwania kolumn z danych źródłowych.

Widziałem, jak inni używają dopasowania i zastępują, zaimplementowałem coś nieco innego niż zwykłe wywołanie funkcji zapytania.

Po pierwsze - utwórz tabelę odnośników wszystkich swoich nazw kolumn w ten sposób. Moja tabela odnośników zaczyna się w kolumnie E arkusza „Config” tylko dlatego, że mam inne elementy na arkuszu, można go łatwo umieścić we własnym arkuszu.

• Kolumna 1 (nazwa kolumny)

  =TRANSPOSE( 'Source Data'!1:1 )
  

• Kolumna 2 (kolumna nr)

  =arrayformula( row( E2:E ) - 1 )
  

• Kolumna 3 (litera kolumny)

  =arrayformula( if( int( F2:F / 26.5 ) > 0, char( int( F2:F / 26.5 )  + 64), "" ) & char( (F2:F - (int( F2:F / 26.5 ) * 26 ) ) + 64 ) )
  

  Jestem otwarty na udoskonalenia w celu konwersji numeru kolumny na literę. Ta formuła jest ograniczona, ponieważ obsługuje tylko 78 kolumn. Ale to dla mnie więcej niż wystarczająco.

Teraz Twoje wywołanie funkcji zapytania będzie wyglądać mniej więcej tak:

=query( 'Source Data'!$A:$L,
"Select " & " " &
vlookup( "Date", Config!$E:$G, 3, false ) & ", " &
vlookup( "Dev Query Engine Conn Count", Config!$E:$G, 3, false ) & ", " &
vlookup( "Dev Conn Limit Retry Count", Config!$E:$G, 3, false ) & ", " &
vlookup( "Dev Max Sequential Retry in One Minute", Config!$E:$G, 3, false ) & ", " &
vlookup( "Dev Conn Limit Errors", Config!$E:$G, 3, false ) & " " &
"where " & vlookup( "Display in Graph.", Config!$E:$G, 3, false ) & "=TRUE " &
"order by " & vlookup( "Date", Config!$E:$G, 3, false ) & " desc "
)

Zachowaj ładnie sformatowany i nie jest wcale taki zły w zarządzaniu. Jedyną rzeczą, którą ucierpi, będzie zmiana indeksu kolumny zakresu widzenia. Ale to nie powinno się często zmieniać, jeśli w ogóle, biorąc pod uwagę, że jest to zasięg pomocnika.

Stare pytanie, ale myślę, że to rozwiązanie może być tego warte.

Możesz użyć niestandardowej funkcji skryptu, która pobiera indeks kolumny (tj. A, B, C ...) za pomocą nazwy nagłówka, umożliwiając wykonanie czegoś takiego:

=query('MySheet'!A2:Z; 
   "select "&colIndex("'MySheet'!A1:Z1"; "Car name")&"  
    where "&colIndex("'MySheet'!A1:Z1"; "Car color")&"='Blue'"))

Z colIndexfunkcją:

function colIndex(a1NotationStr, header){
  var range = SpreadsheetApp.getActiveSpreadsheet().getRange(a1NotationStr)
  var row = range.getValues()[0]

  //parse the header row and stop at first header matching our search
  //use the related cell A1Notation and remove all numbers (i.e. 'A45' becomes 'A')
  for(var i=0; i<row.length; i++){
    var currHeader = row[i]
    if(currHeader == header){
      return range.getCell(1, i+1).getA1Notation().replace(/[0-9]/g, '');
    }  
  }
}

Które mogą być łatwiejsze w użyciu i czytaniu.

Oto moje rozwiązanie:

jeśli masz kolumnę o nazwie „miasto”, utwórz gdzieś nową komórkę z indeksem tej komórki:

   A          B               C        ...         Y               Z        
 --------- ------------ ------------ ------ --------------- --------------- 
  Name        Phone        City        ...    Column Name     Column Index  
  Vidar      12345678     Oslo         ...    Name           A              
  Rupert     32165487     Berlin       ...    Phone          B              
  Sahid      32165487     Colombo      ...    City           C              

Następnie możesz utworzyć nazwany zakres dla komórki Z: 2 o nazwie: „nazwa”, Z: 3 = „telefon”, Z: 4 = „miasto”

w zapytaniu możesz następnie napisać:

=QUERY( Sheet1!A1:C,"SELECT A, B, C where "&name&" = 'Vidar'"

Możesz to połączyć z rozwiązaniem dopasowania adresu, aby nie trzeba było śledzić uporządkowanych kolumn.