Google Sheets va Excel’da FILTER, SORT va UNIQUE funksiyalari

Qisqa javob

FILTER, SORT va UNIQUE — dinamik massiv formulalari. Formulani bitta katakka yozasiz, natija esa kerakli miqdordagi qator va ustunlarga o‘zi «to‘kiladi». Manba jadvalda yangi qatorlar paydo bo‘lganda hisobot avtomatik qayta hisoblanadi.

• FILTER — shartlarga mos keladigan qatorlarni qoldiradi.
• SORT — diapazonni bir yoki bir nechta ustun bo‘yicha tartiblaydi.
• UNIQUE — takrorlarsiz ro‘yxat qaytaradi.

Google Sheets’da bu funksiyalar ancha oldin paydo bo‘lgan, Excel’da esa Microsoft 365 va Excel 2021 hamda undan yangi versiyalarda mavjud. Mintaqaviy sozlamalarga qarab argumentlar vergul o‘rniga nuqtali vergul bilan ajratilishi mumkin.

Quyidagi barcha misollarda buyurtmalar varag‘i: A — sana, B — mijoz, C — shahar, D — menejer, E — summa, F — status.

FILTER: shart bo‘yicha jonli hisobot

Barcha to‘langan buyurtmalar:

=FILTER(A2:F100, F2:F100="To‘langan")

Bir nechta «VA» sharti. Shartlarni bir-biriga ko‘paytiring — bu Excel’da ham, Google Sheets’da ham ishlaydi. Minimal summa I1 katakda:

=FILTER(A2:F100, (F2:F100="To‘langan")*(E2:E100>=I1))

Google Sheets’da shartlarni alohida argumentlar sifatida ham sanab o‘tish mumkin: =FILTER(A2:F, F2:F="To‘langan", E2:E>=I1).

«YOKI» shartlari. Shartlarni qo‘shing:

=FILTER(A2:F100, (C2:C100="Toshkent")+(C2:C100="Samarqand"))

Bo‘sh natija. Hech narsa topilmasa, Excel #CALC! xatosini, Google Sheets esa #N/A qaytaradi. Excel’da uchinchi argumentdan, Sheets’da IFNA’dan foydalaning:

Excel:   =FILTER(A2:F100, F2:F100="To‘langan", "Ma’lumot yo‘q")
Sheets:  =IFNA(FILTER(A2:F, F2:F="To‘langan"), "Ma’lumot yo‘q")

Menejer shartni o‘zi tanlashi uchun matnga emas, ochiladigan ro‘yxatli katakka havola qiling.

SORT: qo‘lda tartiblashsiz tartib

Sintaksis farq qiladi: Google Sheets’da yo‘nalish TRUE yoki FALSE bilan, Excel’da — 1 yoki -1 bilan beriladi.

Sheets:  =SORT(A2:F100, 5, FALSE)
Excel:   =SORT(A2:F100, 5, -1)

Ikkala formula ham buyurtmalarni summa (diapazonning beshinchi ustuni) bo‘yicha kamayish tartibida saralaydi.

Bir nechta ustun bo‘yicha tartiblash — avval shahar alifbo bo‘yicha, keyin summa kamayish tartibida:

Sheets:  =SORT(A2:F100, 3, TRUE, 5, FALSE)
Excel:   =SORTBY(A2:F100, C2:C100, 1, E2:E100, -1)

Excel’da bir nechta kalit uchun SORTBY qulayroq: diapazondan keyin «tartiblash ustuni — yo‘nalish» juftliklari keladi.

UNIQUE: takrorlarsiz mijozlar ro‘yxati

=UNIQUE(B2:B100)

Agar diapazonda bo‘sh kataklar bo‘lsa, natijaga bo‘sh qiymat tushadi (Excel’da — 0). Ularni filtr bilan olib tashlang:

=UNIQUE(FILTER(B2:B100, B2:B100<>""))

Noyob mijozlar soni — =COUNTA(UNIQUE(FILTER(B2:B100, B2:B100<>""))).

UNIQUE’ning uchinchi argumenti TRUE bo‘lsa, faqat aynan bir marta uchraydigan qiymatlar qoladi — masalan, bitta buyurtmasi bor mijozlar:

=UNIQUE(B2:B100, FALSE, TRUE)

Kombinatsiyalar

To‘langan buyurtmalari bor Toshkentlik mijozlarning tartiblangan ro‘yxati:

=SORT(UNIQUE(FILTER(B2:B100, (C2:C100="Toshkent")*(F2:F100="To‘langan"))))

Formulani ichkaridan tashqariga qarab o‘qing: saralash, takrorlarni olib tashlash, tartiblash.

Excel’da «mijoz — summa» jamlanmasi: H2 da mijozlar ro‘yxati =SORT(UNIQUE(B2:B100)), I2 da har biri bo‘yicha summa =SUMIF(B2:B100, H2#, E2:E100). # belgisi butun to‘kilgan diapazonga havola qiladi va jamlanma ro‘yxat bilan birga o‘sadi.

Ko‘p uchraydigan xatolar

• Excel’da #SPILL! yoki Google Sheets’da #REF! — natija to‘kilishi kerak bo‘lgan kataklar band. Formula ostidagi joyni tozalang.
• Diapazonlar o‘lchami har xil. Ma’lumotlar diapazoni va shartlar diapazonlari bir xil qatorlarda boshlanib, tugashi kerak.
• Matn sifatida saqlangan sonlar. E2:E100>=I1 sharti matnli summalarda ishlamaydi — avval ma’lumotlarni songa aylantiring.
• Natija ichida qo‘lda tahrirlash. To‘kilgan diapazon ichiga hech narsa yozmang: bu formulani buzadi.
• Excel’da butun ustunlar. B:B kabi havolalar ishlaydi, lekin kitobni sekinlashtiradi; ma’lumotlarni jadval sifatida rasmiylashtirgan ma’qul.