Media SUMSELGO

Indonesia – Dapatkan berita terbaru tentang Indonesia. Baca Breaking News on Indonesia diperbarui dan diterbitkan di SUMSELGO

Solusi Excel dijelaskan untuk versi yang berbeda

Excel telah berkembang pesat selama dekade terakhir, dan setiap versi berisi solusi berbeda untuk sejumlah masalah.

Baru-baru ini, saya memecahkan masalah tanggal dalam sebuah tabel. Anda memerlukan rumus untuk secara bersyarat menemukan tanggal terdekat yang cocok dengan dua kriteria lain dari tabel. Solusinya tergantung pada versi Excel yang saya gunakan.

Selama sepuluh tahun terakhir, Excel telah berubah secara dramatis. Sekarang, lebih dari sebelumnya, versi perangkat lunak Anda menentukan apa yang dapat Anda lakukan dan seberapa mudah solusinya.

Jadwal yang saya kerjakan ditampilkan di Gambar 01.

Saya perlu mengetahui tanggal revisi berikutnya untuk setiap proyek. Ini berarti saya memerlukan tanggal terdekat di kolom D, karena ada sel kosong di kolom E.

Laporan saya ditampilkan di Gambar 02. Kolom memiliki empat solusi berbeda. Baris 1 berisi versi yang berfungsi dengannya. Grade 2 menjelaskan solusinya.

Gambar 02.

Semua formula berasal dari kelas 4 in Gambar 02 muncul di Gambar 03.

Gambar 03.

Semua rumus baris 4 telah disalin. Anda perlu merujuk ke Gambar 03 Saya juga menjelaskan rumus ini. Rumusnya cukup rumit, dan saya memberikan detail lebih lanjut dalam video yang menyertai artikel ini.

Rumus dari sel K9 belum ditampilkan dan akan diselesaikan.

Untuk menentukan atau mengembalikan sel kosong, Anda bisa menggunakan tanda kutip ganda bersama-sama seperti ini: “”.

Semua versi – array – kolom H.

Rumus array lebih kompleks daripada kebanyakan rumus. Rumus array bisa bekerja dengan rentang dengan cara yang sama seperti rumus standar untuk sel tunggal. Anda harus menggunakan set keyboard khusus untuk memasukkan array.

Tahan tombol Ctrl dan Shift lalu tekan tombol Enter. Ini menempatkan tanda kurung (kurung kurawal) di sekitar rumus. Anda dapat melihatnya di versi pertama yang terdaftar di Gambar 03.

Rumus array ini pertama kali menghitung fungsi IF pertama. Ini membandingkan setiap sel dalam rentang A2: A13 dengan kode proyek di sel G4. Jika sel cocok dengan kode proyek, itu menavigasi ke fungsi IF berikutnya dan mencari di baris yang sesuai dalam rentang E2: 13 dari sel kosong.

READ  Zoom memiliki banyak peningkatan yang mematikan - inilah yang baru

Jika menemukan sel kosong, ia mengembalikan baris yang sesuai dari D2: D13. Jika salah satu tes FALSE, sel kosong dikembalikan.

Bersama-sama, fungsi IF menyediakan rentang tanggal dan sel kosong untuk fungsi MIN (yang mengembalikan nilai minimum) untuk Anda tinjau. Fungsi MIN mengabaikan sel kosong.

Tanggal yang tersisa dibandingkan untuk menentukan tanggal paling awal (terendah). Jenis penghitungan yang menggunakan rumus normal ini membutuhkan beberapa sel untuk dijalankan. Rumus array dapat melakukan semua perhitungan dalam satu sel.

Excel 2010 dan versi yang lebih baru – pengelompokan – kolom pertama

Fungsi AGGREGATE memiliki kemampuan yang tidak biasa untuk mengabaikan sel kesalahan. Kemampuan ini memungkinkan kita membuat rumus tunggal yang berfungsi sebagai rumus array tetapi bukan array.

Fungsi AGGREGATE sama dengan fungsi SUBTOTAL. Anda menentukan penghitungan yang akan dilakukan. Angka “15” di awal mendefinisikan fungsi SMALL. Fungsi SMALL adalah versi fleksibel dari fungsi MIN.

Fungsi MIN mengembalikan nilai minimum dalam suatu rentang. Fungsi SMALL dapat menemukan yang terendah, atau terendah kedua, dengan menentukan nomor posisi: 1 menemukan terendah, 2 detik terendah.

Argumen kedua AGGREGATE memiliki 6, dan ini memerintahkannya untuk mengabaikan kesalahan.

Di Excel, tanggal adalah angka. Setiap tanggal memiliki nomor seri utama. Dalam fungsi AGREGATE, kami membagi rentang tanggal dengan hasil penghitungan bersyarat. Akun ini melihat dua kondisi terpisah. Begini cara kerja penghitungan ini.

Setiap sel dalam rentang dapat mengembalikan TRUE atau FALSE. Ketika kita mengalikan kedua kondisi bersama-sama, mereka mengubah TRUE menjadi 1 dan FALSE menjadi 0. Dan hasil dari A2 akan dikalikan dengan hasil dari E2 dan seterusnya di bawah rentang.

Dalam dua kondisi, ada empat kemungkinan dan dua kemungkinan hasil, seperti yang ditunjukkan pada Gambar 04.

Gambar 04.

Saat Anda mengalikan apa pun dengan nol (FALSE), hasilnya nol, seperti yang Anda lihat di Gambar 04. Satu-satunya waktu 1 kembali adalah ketika kedua kondisi BENAR.

READ  Komitmen lima tahun Microsoft untuk aksesibilitas dimulai dengan peningkatan AI untuk Office dan banyak lagi

Kami membagi tanggal pada hasil untuk kasus ini. Setiap kondisi yang tidak cocok akan menjadi nol. Pembagian dengan nol mengembalikan kesalahan.

Kami mengarahkan fungsi AGREGATE untuk mengabaikan kesalahan. Jika kedua ketentuan cocok, tanggal akan dibagi 1, membiarkannya tidak berubah. Tanggal yang tersisa akan cocok dengan kedua kriteria tersebut. Ini dianalisis dengan fungsi SMALL untuk menentukan tanggal minimum.

Angka 1 di akhir fungsi AGGREGATE mengarahkan fungsi SMALL untuk mengembalikan nilai tanggal terendah (terlama).

Fungsi IFERROR telah ditambahkan untuk menangani kesalahan yang dihasilkan ketika tidak ada kondisi yang terpenuhi.

Ini terjadi jika tidak ada sel kosong untuk kode proyek tertentu.

Excel 2019 dan yang lebih baru – MINIFS – Solusi pilihan – Kolom J.

Sama seperti SUMIFS yang memungkinkan pengelompokan bersyarat, MINIFS memungkinkan penghitungan minimum bersyarat.

Rentang pertama, D2: D13, adalah rentang di mana batas bawah ditemukan.

Kemudian Anda mengaitkan rentang ketentuan, A2: A13, dengan ketentuan G4. Anda dapat menambahkan ketentuan dengan menambahkan rentang ketentuan lain, E2: E13, dan ketentuan lain, “”. Ini semua dipisahkan dengan koma.

Karena fungsi ini dibuat untuk menangani kebutuhan kami, ini adalah solusi yang disarankan. Sayangnya, ini hanya tersedia di Excel 2019 dan versi langganan. Perhatikan bahwa ada fungsi MAXIFS juga.

Edisi Hanya Langganan – Array Dinamis – Kolom K.

Versi langganan Excel tersedia melalui Microsoft 365 (sebelumnya Office 365). Versi ini menerima pembaruan rutin. Pada tahun 2020, ia menerima pembaruan Array Dinamis, yang saya bahas dalam tiga artikel terpisah pada bulan Mei, Juni, dan Juli 2020.

Matriks dinamis mengubah cara rumus dihitung. Untuk rumus array, Anda tidak perlu lagi menggunakan Ctrl + Shift + Enter untuk memasukkannya. Rumus dalam sel H4 dan K4 identik kecuali tanda kurung di kedua ujungnya.

Perhitungan dalam versi langganan bekerja dengan cara yang sama seperti rumus array yang saya jelaskan sebelumnya. Saya tidak merekomendasikan solusi matriks dinamis ini, karena kami memiliki fungsionalitas MINIFS khusus yang tersedia dalam versi langganan.

READ  Demo Resident Evil Village memberi kami perbandingan kinerja antara rilis PS5 dan PS4

Rumus ini memperlihatkan bahwa mesin kalkulasi baru Excel bisa menangani rumus sel tunggal yang bekerja dengan kondisi berdasarkan rentang, bukan hanya sel tunggal. Saya telah menyertakannya, sehingga Anda dapat melihat bagaimana kami dapat menyesuaikannya untuk menangani persyaratan yang lebih menantang.

Misalkan alih-alih mencari tanggal pertama berdasarkan kode proyek, kita ingin mencari tanggal pertama berdasarkan huruf pertama kode akun di kolom C. Awalan yang berbeda dapat dikaitkan dengan kategori akun yang berbeda. Gambar 05 Dia punya laporannya. Rumusnya termasuk dalam K9 in Gambar 03. MINIFS tidak dapat menangani situasi seperti ini.

Gambar 05.

Array dinamis memungkinkan Anda memanipulasi rentang dengan fungsi lain. LEFT mengekstrak karakter dari kiri sel. Kita bisa menggunakannya untuk mengekstrak huruf pertama dari rentang kolom kalkulasi.

Kita dapat mengganti kondisi kode proyek dengan kondisi yang mengekstrak huruf pertama dari setiap sel dalam rentang C2: C13. Ini kemudian dibandingkan dengan huruf di sel J9 untuk mengembalikan TRUE atau FALSE untuk setiap sel dalam rentang.

Excel telah berkembang selama bertahun-tahun. Kami beralih dari rumus array kompleks yang memerlukan input keyboard khusus ke fungsi yang berfungsi seperti array, lalu ke fungsi kustom yang dibuat untuk menangani penghitungan bersyarat minimal.

Terakhir, Excel memiliki rumus yang dapat menangani kondisi fleksibel dalam rentang menggunakan larik dinamis.

Ingatlah untuk menonton video yang menyertai artikel ini, yang akan menjelaskan lebih detail tentang cara kerja rumus ini.

Video yang menyertai dan file Excel akan menjelaskan lebih rinci untuk menggambarkan teknik ini.

Neale Blackwood CPA menjalankan A4 Accounting, menyediakan pelatihan Excel, webinar, dan layanan konsultasi. Pertanyaan dapat dikirim ke [email protected]