Rentang Tanggal dalam Fungsi Kriteria

Laporan nyata hampir selalu menanyakan tentang rentang waktu: penjualan pada kuartal ini, pesanan bulan lalu, atau pendaftaran antara dua tanggal. Fungsi kriteria yang telah Anda pelajari — SUMIFS, COUNTIFS, AVERAGEIFS — dapat menangani hal ini dengan baik setelah Anda mengetahui cara menyatakan rentang tanggal.

Triknya adalah menyadari bahwa rentang tanggal sebenarnya merupakan dua kondisi pada kolom tanggal yang sama: pada atau setelah tanggal mulai, dan pada atau sebelum tanggal akhir.

Spreadsheet menyimpan tanggal sebagai nomor seri — hari ke-1 adalah 1 Januari 1900 (atau 1899 di Sheets), dan setiap hari berikutnya menambah satu. Karena itu, Anda dapat membandingkan tanggal dengan > dan < persis seperti angka biasa.

Jadi, setelah 1 Januari cukup berarti nomor seri yang lebih besar daripada nomor seri tanggal tersebut. Inilah pemahaman utama yang membuat pemfilteran tanggal dapat dilakukan.

Misalkan kolom A berisi Tanggal Pesanan dan kolom C berisi Jumlah. Untuk menjumlahkan penjualan dari Januari 2024, gunakan kolom A dua kali: untuk tanggal pada atau setelah 1 Januari, dan tanggal pada atau sebelum 31 Januari.

Bungkus tanggal dengan fungsi DATE(year, month, day) agar tidak ambigu dalam berbagai format regional. Kedua kondisi tersebut membentuk AND dan hanya mengambil baris pada bulan itu.

Anda mungkin mencoba mengetikkan ">=1/1/2024" secara langsung. Cara ini sering berhasil, tetapi rentan terhadap masalah — spreadsheet mungkin membacanya sebagai teks atau salah menafsirkan urutan hari dan bulan.

Pola yang andal adalah ">="&DATE(2024,1,1). Fungsi DATE membuat nomor seri yang nyata, sedangkan & menggabungkan operator dengannya. Cara ini dapat diandalkan baik di Excel maupun Google Sheets, dalam lokal mana pun.

Tanggal yang ditetapkan langsung cocok untuk laporan tetap, tetapi laporan yang fleksibel membaca tanggal mulai dan akhir dari sel. Masukkan tanggal mulai di F1 dan tanggal akhir di F2.

Sekarang periode ditentukan oleh lembar kerja. Ubah F1 atau F2, lalu setiap total akan dihitung ulang. Seperti biasa, gabungkan operator dengan sel menggunakan & — jangan pernah menempatkan nama sel di dalam tanda kutip.

Terkadang Anda hanya memerlukan satu batas. Semua data mulai dari suatu tanggal dan seterusnya menggunakan satu kondisi lebih besar dari atau sama dengan. Semua data hingga suatu tanggal menggunakan satu kondisi lebih kecil dari atau sama dengan.

Ini menghitung semua pesanan yang dibuat pada atau setelah tanggal di F1, tanpa batas atas — berguna untuk metrik seperti "penjualan sejak peluncuran".

Kondisi tanggal dapat dipadukan dengan bebas dengan kondisi teks dan angka. Untuk menjumlahkan penjualan wilayah Timur dalam suatu rentang tanggal, tambahkan pasangan wilayah di samping kedua pasangan tanggal.

Urutannya tidak memengaruhi hasil — Excel mengevaluasi semua kondisi sebagai satu AND besar. Di sini, tiga pasangan kriteria menggunakan rentang rata-rata atau rentang jumlah yang sama.

Untuk menjumlahkan satu tahun penuh, tetapkan batas ke hari pertama dan hari terakhir tahun tersebut. Gunakan DATE untuk awal periode dan tanggal akhir periode.

Untuk satu bulan, gunakan hari pertama bulan tersebut sebagai batas bawah dan hari pertama bulan berikutnya dengan "<" tegas sebagai batas atas — cara praktis untuk menghindari kekhawatiran tentang jumlah hari 28, 30, atau 31.

Untuk laporan bergulir, buat batas dari TODAY(). Untuk menghitung pesanan dalam 30 hari terakhir, batas bawahnya adalah hari ini dikurangi 30 dan batas atasnya adalah hari ini.

Karena TODAY() diperbarui setiap hari saat lembar kerja menghitung ulang, rentang tersebut bergeser ke depan secara otomatis — tidak diperlukan pengeditan manual.

Jika kolom tanggal Anda sebenarnya menyimpan tanggal dan waktu (seperti stempel waktu), baris bertanggal pada waktu yang larut di 31 Januari memiliki nomor seri yang sedikit lebih besar daripada nilai satu hari penuh untuk 31 Januari. Batas "<="&DATE(2024,1,31) akan mengecualikannya.

Solusi amannya adalah pola kurang-dari-tegas untuk hari berikutnya: "<"&DATE(2024,2,1) mencakup setiap waktu dalam bulan Januari, termasuk stempel waktu.

Pola rentang tanggal yang sama juga berfungsi dengan AVERAGEIFS. Untuk menemukan rata-rata nilai pesanan dalam suatu rentang waktu, berikan kolom jumlah sebagai rentang rata-rata dan tambahkan kedua kondisi tanggal pada kolom tanggal.

Ingat jebakan periode kosong: jika tidak ada pesanan dalam rentang tanggal tersebut, AVERAGEIFS mengembalikan #DIV/0!. Membungkusnya dengan IFERROR membuat dasbor yang difilter berdasarkan waktu tetap rapi saat suatu periode tidak memiliki data.

Sekarang Anda dapat memfilter SUMIFS, COUNTIFS, dan AVERAGEIFS berdasarkan waktu:

• Rentang tanggal adalah dua kondisi pada kolom tanggal yang sama (>= mulai dan <= akhir).
• Buat tanggal dengan DATE(y,m,d) dan gabungkan operator menggunakan ">="&.
• Untuk bulan, gunakan batas atas kurang dari yang tegas untuk hari berikutnya ("<"&DATE(...)) guna menangani stempel waktu.
• Gunakan TODAY() untuk rentang bergulir seperti 30 hari terakhir.

Dengan demikian, pembahasan tentang keluarga IFS dengan banyak kriteria selesai.