QUERY, Google E-Tablolar’da bir veri aralığından belirli sütunları seçmek, koşula uyan kayıtları almak veya gruplara göre toplam üretmek için kullanılabilir. Sorgu metni SQL’e benzeyen bir dil kullanır, ancak bütün SQL özelliklerini destekleyen bir veritabanı sistemi değildir. Güvenilir sonuç için aralık, başlık sayısı ve sütun türleri birlikte düşünülmelidir. Bu rehber aynı küçük hareket listesini kullanarak önce sütun seçmeyi, sonra filtrelemeyi ve gruplamayı kurar. Her adımda beklenen sonucu kaynak kayıtlarla karşılaştıracağız. Karmaşık tek bir sorguyla başlamak yerine çalışan küçük adımları birleştirmek hatayı bulmayı kolaylaştırır.
Bu rehberde neler var?
Örnek veriyi ve başlık sayısını hazırlayın
A sütununda Ürün, B sütununda Şube ve C sütununda Miktar olsun. İkinci satırda Defter, Merkez, 10; üçüncüde Kalem, Merkez, 20; dördüncüde Defter, Şube, 15; beşincide Kalem, Şube, 5 bulunsun. Dört hareketin toplam miktarı 50’dir. Her satır tek hareketi temsil eder. Miktar sütununda açıklama metni veya birim adı bulunmasın. Birim gerekiyorsa ayrı sütun kullanın. Kaynakta aynı ürün adı farklı biçimde yazılıyorsa sorgu bunları kendiliğinden tek ürün olarak anlayamaz. Önce tanımların tutarlı olmasını sağlayın ve başlık satırını açıkça belirleyin.
Basit örnek =QUERY(A1:C5;"select A,C";1) şeklindedir. İlk alan kaynak aralığı, ikinci alan sorgu metni, üçüncü alan başlık satırı sayısıdır. Burada kaynak bir başlık satırı içerdiği için 1 kullanılır. Ayırıcı uygulamanın yerel ayarına göre virgül olabilir. QUERY ve sorgu içindeki select gibi anahtar sözcükler örneklerde İngilizce yazılmıştır; bunlar Türkçe cümlelere çevrilmez. Otomatik başlık tahminine güvenmek yerine bilinen başlık sayısını açıkça vermek daha öngörülebilir sonuç sağlar. Sonuç için kaynak dışında yeterli boş alan ayırın; dizi hücrelere yayılacaktır.
Koşul ekleyerek kaynak listesini daraltın
Yalnızca Merkez şubesinin kayıtlarını almak için =QUERY(A1:C5;"select A,C where B = 'Merkez'";1) kullanabilirsiniz. Sorgu metni dışta çift tırnak, içte metin sabiti tek tırnak içerir. Beklenen iki kayıt Defter 10 ve Kalem 20’dir. Sayısal koşulda ise sayı metin gibi tırnak içine alınmaz. Örneğin miktarı 10’dan büyük kayıtları ararken C > 10 ifadesi kullanılır. Sınırdaki 10 değerinin dahil edilmesini istiyorsanız karşılaştırma işaretini buna göre seçin. Küçük örnekte 10, 15 ve 20 sonuçlarını önceden düşünerek kontrol edin.
Birden fazla koşul and veya or ile bağlanabilir. Merkez şubesinde miktarı 10’dan büyük kayıt istediğinizde yalnızca Kalem 20 kalmalıdır. Parantezlerle koşulların birlikte değerlendirilme sırasını açık tutun. Farklı kişilerin düzenlediği dosyada uzun sorgu metinleri kolayca anlaşılmaz hâle gelebilir. Sorgunun üstüne hangi kayıtların istendiğini düz cümleyle yazmak bakım işini kolaylaştırır. Giriş hücresinden metin alıp sorguya ekliyorsanız tırnak içeren kullanıcı değerlerinin sorguyu bozabileceğini düşünün. Kontrolsüz metin birleştirmek yerine sınırlı seçenekler veya daha uygun FILTER yaklaşımı kullanabilirsiniz.
Ürüne göre toplama ve sıralama yapın
Ürün başına miktar toplamı için =QUERY(A1:C5;"select A,sum(C) group by A";1) örneğini deneyin. Defter toplamı 25, Kalem toplamı 25 olmalıdır. Gruplanmamış başka sütunları aynı seçimde gelişigüzel kullanmayın. Toplanmayan seçili sütunların gruplama mantığıyla uyumlu olması gerekir. Şubeyi de görmek istiyorsanız hem ürün hem şube bazında gruplama yapmanız gerektiğini değerlendirin. Tek satır ürün özeti ile ürün ve şube kırılımı farklı raporlardır. Bu ayrımı çözmeden sorguya yeni sütun eklemek hata veya beklenmedik satır çoğalması yaratabilir.
Sonucu toplam miktara göre sıralamak için sorguya uygun order by ifadesi eklenebilir. Eşit toplamlı ürünlerde hangi sıranın beklendiğini de düşünün; ikinci bir sıralama ölçütü gerekebilir. Başlıkların okunaklı olması için label bölümüyle hesaplanan sütun adını değiştirebilirsiniz. Örneğin sum(C) için Toplam Miktar etiketi kullanmak raporu anlaşılır kılar. Ancak etiketi değiştirmek hesabın türünü değiştirmez. Sayım sonucu verdiğiniz sütuna Toplam yazmak yanlış anlam yaratır. Önce gerçekten sum, count veya avg ile hangi ölçünün üretildiğini doğrulayın, sonra kullanıcıya uygun başlığı verin.
Karışık veri türlerine dikkat edin
QUERY sütunları veri türlerine göre yorumlar. Aynı sütunda sayı ve metin karıştığında azınlıkta kalan türdeki değerler boş kabul edilebilir. Bu nedenle ekranda görünen bütün miktarların toplama girdiğini varsaymayın. Örneğin dört sayının arasına açıklama eklemek veya rakamı metin olarak saklamak beklenen kapsamı bozabilir. Kaynakta sayı gibi görünen değerin gerçekten sayı olduğunu kontrol edin. Bir sorgunun hata vermeden çalışması bütün kayıtları doğru değerlendirdiğini kanıtlamaz. Toplam 50 beklediğiniz küçük örnekte kaynak hücrelerin türünü değiştirip sonucu incelemek bu riski görünür kılar.
Tarih koşullarında sorgu dilinin tarih sabiti biçimi ile çalışma sayfasının görünen tarih biçimi farklı olabilir. Gün ay sırasını tahmin ederek sorgu yazmayın; resmî sorgu dili açıklamasındaki date kullanımı ve ISO biçimini inceleyin. Gerçek tarih alanına metin tarih karıştırmak sonuçları etkiler. Kaynak doğrudan hücre aralığı değil de üretilmiş bir dizi olduğunda sütun adlandırması A, B, C yerine Col1, Col2 gibi olabilir. İnternetten alınan örneği kendi aralık türünüze uyarlayın. Başvuru biçimi farklıysa yalnızca sütun harfini değiştirmek yeterli olmayabilir; kaynağın nasıl kurulduğunu anlamanız gerekir.
Kapsam büyümesini ve boş satırları kontrol edin
Yeni hareketi altıncı satıra eklediğinizde A1:C5 aralığı bunu kapsamaz. Formülü gerçek veri kapsamınıza uygun biçimde genişletin. Bütün sütunları kullanmak pratik görünse de gereksiz alan ve boş kayıt davranışı düşünülmelidir. Gerekli durumda is not null gibi koşullarla gerçek kayıtları ayırın; hangi alanın zorunlu anahtar olduğunu belirleyin. Miktarı sıfır olan geçerli kayıtlar ile boş ürün kodunu aynı filtreyle kaldırmayın. Bir raporun yeni verileri kapsadığını yalnızca genel toplamdan değil, yeni işlem kimliğinin sonuçta görünmesinden de kontrol edin.
Son testte sütun seçimi, tek koşul, iki koşul, gruplama ve yeni kayıt için ayrı beklentiler yazın. Ürün toplamlarının genel toplamı 50 ile uyuşmalıdır; ancak bu eşitlik satırların tamamının doğru eşleştiğini tek başına kanıtlamaz. Her gruptan bir kaydı kaynakla karşılaştırın. Dosya paylaşımını erişim rehberindeki rollere göre yapın; sorgu ile gösterilmeyen satırlar aynı dosyada bulunmaya devam edebilir. Sadece görsel inceleme için filtre görünümü daha basit olabilir. İhtiyaç yalnızca sıralı listeyse gereksiz karmaşık sorgu kurmayın; anlaşılabilir en küçük yapıyla başlayın.
Kaynak ve kapsam
Başvuru: Google QUERY işlevi ve sorgu dili başvurusu. Desteklenen sorgu ifadelerini bu belgelerden doğrulayın.