ChatGPT dan Excel: bina formula, semak jawapan dan cari kesilapan

Terangkan maksud jumlah sebelum meminta formula

ChatGPT boleh membantu mencadangkan formula apabila struktur data dan soalan diterangkan dengan tepat. Namun, formula yang berjaya dikira belum tentu menjawab soalan laporan. “Jumlah pendaftaran” boleh bermaksud bilangan baris, bilangan ID unik atau bilangan peserta. Tentukan maksud itu sebelum menyalin formula.

Latihan ini menggunakan lapan baris pendaftaran rekaan untuk program AI dan Excel. Ada satu ID berulang, satu status kosong dan satu pendaftaran dibatalkan. Matlamatnya ialah mengira subset pendaftaran Confirmed yang mempunyai ID unik, sambil menunjukkan rekod yang memerlukan semakan.

Muat turun workbook untuk melihat formula dan panduan, atau gunakan CSV jika mahu membina semula latihan. Jawapan rujukan dikira daripada data dan peraturan yang dinyatakan. Ia tidak bergantung pada sama ada AI memberikan jawapan yang serupa.

Kenali data dan tetapkan peraturan rekod bermasalah

Helaian Data menyimpan ID, Tarikh, Program, Status, Peserta dan Kadar_RM dalam lajur A hingga F. Baris data ialah 2 hingga 9. Satu baris mewakili satu rekod pendaftaran; Peserta ialah jumlah orang dalam rekod tersebut. Jangan mengira lapan baris sebagai lapan peserta.

ID R004 muncul dua kali dengan nilai yang sama. Untuk latihan ini, kedua-dua baris dikecualikan sementara daripada subset Confirmed sehingga diperiksa. Ini ialah peraturan latihan yang sengaja konservatif; ia bukan arahan memadam semua rekod berulang dalam setiap dataset. Pemilik data sebenar perlu menentukan sama ada sesuatu ulangan ialah kesilapan atau transaksi yang sah.

Status kosong tidak dianggap Confirmed. Rekod Cancelled juga dikecualikan daripada subset tersebut. R006 mempunyai sifar peserta dan dikekalkan sebagai rekod sah bernilai sifar. Perbezaan antara kosong dan sifar perlu dijelaskan supaya formula tidak menyembunyikan isu sumber.

Berikan ChatGPT peta lajur dan syarat yang tepat

Sertakan nama helaian, julat data dan peraturan pengecualian. Minta penjelasan setiap bahagian formula supaya anda boleh menilai cadangannya. Elakkan memberikan seluruh fail kerja sebenar jika contoh kecil sudah mencukupi untuk menerangkan masalah.

Arahan di bawah menggunakan lajur bantuan supaya semakan boleh dilihat pada setiap baris. Kaedah ini juga memudahkan perbincangan dalam pasukan: seseorang boleh menunjukkan sebab satu rekod dimasukkan atau ditangguhkan tanpa perlu mentafsir satu formula yang panjang.

Data rekaan berada pada helaian Data, baris 2 hingga 9.
A=ID, B=Tarikh, C=Program, D=Status, E=Peserta, F=Kadar_RM.
Cadangkan formula bagi G (Bil_ID) dan H (Nilai_Baris_RM).
Kemudian kira jumlah peserta dan nilai bagi Status="Confirmed" DAN Bil_ID=1.
Kedua-dua baris ID berulang mesti dikecualikan sementara, bukan kekalkan satu secara automatik.
Status kosong dan Cancelled tidak termasuk. Sifar peserta ialah nilai sah.
Terangkan formula dan tunjukkan rekod yang menyumbang kepada jumlah.

Bina formula yang boleh diperiksa baris demi baris

Dalam G2, COUNTIFS mengira berapa kali ID pada A2 muncul dalam julat sumber. Tanda dolar mengekalkan julat ketika formula disalin ke bawah. Dalam H2, bilangan peserta didarab dengan kadar untuk mendapatkan nilai baris. Salin kedua-duanya hingga baris 9.

Formula SUMIFS kemudian menjumlahkan hanya baris dengan status Confirmed dan Bil_ID bersamaan satu. Microsoft menerangkan SUMIFS sebagai penjumlahan menggunakan beberapa syarat. Pastikan semua julat syarat dan julat jumlah mempunyai panjang yang sama; dalam contoh ini semuanya menggunakan baris 2 hingga 9.

Jika susunan lajur dalam fail anda berbeza, rujukan formula juga mesti berubah. Jangan menyalin formula ini ke workbook lain tanpa memadankan nama helaian dan medan. Label “Kadar_RM” dalam contoh bermaksud kadar bagi setiap peserta, jadi unit pengiraan juga penting.

Data!G2: =COUNTIFS($A$2:$A$9,A2)
Data!H2: =E2*F2

Jumlah nilai subset:
=SUMIFS(Data!$H$2:$H$9,Data!$D$2:$D$9,"Confirmed",Data!$G$2:$G$9,1)

Jumlah peserta subset:
=SUMIFS(Data!$E$2:$E$9,Data!$D$2:$D$9,"Confirmed",Data!$G$2:$G$9,1)

Bandingkan dengan jumlah rujukan yang jelas maksudnya

Empat rekod yang memenuhi syarat memberikan 25 peserta dan nilai RM3,240. Semakan tangan ialah RM1,200 untuk R001, RM1,200 untuk R002, RM0 untuk R006 dan RM840 untuk R007. Jumlah ini ialah subset yang boleh dilaporkan mengikut peraturan latihan, sementara rekod bermasalah masih menunggu semakan.

Jika semua baris didarab dan dijumlahkan, hasilnya RM7,620. Jika hanya status Confirmed digunakan tanpa pemeriksaan ID, hasilnya RM6,120. Kedua-dua pengiraan itu boleh berjalan tanpa ralat Excel tetapi menjawab soalan yang berbeza. Jangan melabel mana-mana jumlah ini sebagai hasil bulan sebenar.

Uji perubahan sebelum menggunakan kaedah pada kerja sebenar

Buat salinan latihan dan ubah satu nilai pada satu masa. Jika peserta R001 bertambah daripada 10 kepada 11, jumlah subset sepatutnya bertambah seorang dan RM120. Jika R007 ditukar kepada Cancelled, tujuh peserta dan RM840 sepatutnya keluar daripada jumlah. Pulihkan data asal selepas setiap ujian supaya kesannya jelas.

Seterusnya, cuba tambah rekod baharu di luar baris 9. Formula julat tetap ini tidak merangkuminya secara automatik. Perluaskan semua julat secara konsisten atau bina Excel Table yang sesuai sebelum menjadikan workbook itu proses bulanan. Perubahan sumber ialah sebahagian daripada ujian, bukan langkah yang boleh diabaikan kerana formula pertama sudah betul.

Simpan penerangan definisi, sebab pengecualian dan arahan refresh bersama workbook. ChatGPT boleh membantu menulis nota itu, tetapi manusia yang mengurus data perlu mengesahkan peraturannya. Laporan yang boleh diterangkan dan diulang lebih berguna daripada formula yang hanya difahami oleh orang yang menyalinnya.