Konten
Jika Anda memiliki daftar besar yang diurutkan berdasarkan beberapa kolom, maka alangkah baiknya untuk secara otomatis memisahkan kumpulan baris yang dihasilkan dengan memisahkan garis horizontal untuk kejelasan:
Dalam contoh di atas, ini adalah garis antar negara, tetapi, secara umum, antara item yang berulang di kolom yang sama. Mari kita lihat beberapa cara untuk menerapkan ini.
Metode 1. Sederhana
Cara tercepat untuk melakukan ini sangat mudah dengan pemformatan bersyarat, yang akan menggambar batas bawah sel jika isi sel di kolom A tidak sama dengan isi sel berikutnya di kolom yang sama. Pilih semua sel dalam tabel kecuali untuk header dan pilih Utama tab perintah Pemformatan Bersyarat – Buat Aturan (Beranda – Pemformatan Bersyarat — Aturan Baru). Pilih jenis aturan Gunakan rumus untuk menentukan sel mana yang akan diformat (Gunakan rumus untuk menentukan sel mana yang akan diformat) dan masukkan rumus berikut di bidang:
Perhatikan dolar di alamat untuk memperbaiki huruf kolom, tetapi bukan nomor baris, karena. kami hanya membandingkan negara di kolom A. Seharusnya tidak ada spasi dalam rumus.
Klik tombolnya Kerangka (format) dan di jendela yang terbuka di tab Batas (Perbatasan) nyalakan garis warna yang diinginkan di batas bawah. Setelah mengklik OK aturan kami akan berfungsi dan garis putus-putus horizontal akan muncul di antara kelompok garis
Metode 2. Dengan dukungan filter untuk angka dan tanggal
Kerugian kecil tetapi sangat mencolok dari metode pertama adalah bahwa batas seperti itu tidak akan selalu berfungsi dengan benar saat memfilter daftar dengan kolom lain. Jadi, misalnya, jika kita memfilter tabel berdasarkan tanggal (hanya Januari), maka garis tidak akan lagi terlihat di antara semua negara, seperti sebelumnya:
Dalam hal ini, Anda bisa keluar menggunakan fungsi SUBTOTAL (SUBTOTAL), yang dapat melakukan berbagai operasi matematika (jumlah, rata-rata, penghitungan, dll.), tetapi "lihat" hanya sel yang difilter. Misalnya, mari kita mengurutkan tabel kita berdasarkan kolom terakhir dengan tanggal dan menggambar garis pemisah di antara hari-hari tersebut. Dalam pemformatan bersyarat, Anda harus membuat aturan yang mirip dengan metode pertama, tetapi tidak menggunakan tautan langsung dalam membandingkan sel D2 dan D3, tetapi menyertakannya sebagai argumen dalam fungsi SUBTOTAL:
Argumen pertama dari fungsi (nomor 109) adalah opcode penjumlahan. Faktanya, kami tidak menambahkan apa pun di sini dan melakukan, pada kenyataannya, operasi bodoh seperti SUM (D2), yang, tentu saja, sama dengan D2. Tetapi fungsi ini berbeda dari SUM tepatnya karena ia melakukan tindakan hanya pada sel yang terlihat, yaitu dan sel yang tersisa setelah filter di layar akan dibandingkan, yang kita inginkan.
Metode 3. Dengan dukungan filter untuk data apa pun
Seperti yang dapat Anda lihat dengan mudah, metode kedua juga memiliki kelemahan: fungsi penjumlahan hanya dapat diterapkan pada angka atau tanggal (yang juga merupakan angka di Excel), tetapi tidak pada teks. Artinya, jika kita ingin menarik garis antar negara, seperti pada metode pertama, tetapi agar ditampilkan dengan benar setelah penyaringan, maka kita harus menggunakan jalur yang jauh lebih rumit. Pilih seluruh tabel lagi kecuali untuk header, buat aturan baru berdasarkan rumus dan masukkan konstruksi berikut di bidang validasi:
=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;СМЕЩ($A$1:$A2;СТРОКА($A$1:$A2)-МИН(СТРОКА($A$1:$A2));;1));—($A$1:$A2=$A2))=1
Dalam versi bahasa Inggris itu akan menjadi:
=SUMPRODUCT(SUBTOTAL(103;OFFSET($A$1:$A2;ROW($A$1:$A2)-MIN(ROW($A$1:$A2));;1));—($A$1:$A2=$A2))=1
Dengan mengklik tombol Kerangka (format) atur batas dengan garis merah di atas dan klik OK. Pembagian yang dihasilkan berdasarkan negara akan berfungsi dengan benar bahkan setelah pemfilteran, misalnya, menurut tanggal:
- Sorot Tanggal dan Waktu dengan Pemformatan Bersyarat
- Bagaimana Excel benar-benar bekerja dengan tanggal dan waktu
- Cara menggunakan pemformatan bersyarat untuk menyorot sel berdasarkan kondisi di Excel