Berkongsi kemahiran Excel praktikal: susun data kehadiran dengan cepat!

Dalam artikel sebelumnya "Pembelajaran carta Excel: Cipta histogram berbilang keadaan berbilang siri dengan nilai sasaran", kami mengetahui tentang tutorial carta Excel, dan hari ini kami berkongsi artikel tentang mengatur data kehadiran. tutorial hanyalah dua formula mudah Sebaik sahaja anda mempelajarinya, anda tidak akan takut tidak kira berapa ramai orang yang ada dalam syarikat anda.

Mesin kehadiran digunakan oleh hampir setiap syarikat, dan pada awal dan akhir setiap bulan, kami memerlukan banyak masa untuk menyusun data kehadiran yang dieksport oleh hari ini kami akan mengajar anda Dengan dua formula, anda boleh menyusun data kehadiran dalam satu minit.

Data kehadiran yang biasanya kami eksport dari mesin kehadiran pada asasnya adalah dalam format berikut apabila dibuka melalui Excel:

Untuk menganalisis data kehadiran dengan lebih lanjut , kami berharap untuk Format di atas disusun mengikut kesan berikut:

Tebuk masuk harian setiap orang direkodkan pada baris yang sama, dan keabnormalan ditandakan selepas perbandingan berdasarkan masa berulang alik.

Waktu bekerja adalah seperti berikut:

Waktu bermula pagi ialah 8:00, waktu tamat pagi ialah 12:00, waktu mula tengah hari ialah 13:30, waktu tamat petang ialah 17:30

Untuk mencapai penukaran kesan ini, ia nampaknya satu perkara yang sangat menyusahkan, selagi anda menguasai dua formula dan beberapa kemahiran operasi asas, ia boleh diselesaikan dalam satu minit ia.

1. Organisasi data asas

Tambahkan beberapa lajur di sebelah kanan data asas untuk membahagikan masa kepada kerja pagi, dapatkan pagi di luar kerja, dan kerja petang dan cuti petang, dan tandakan masa yang sepadan:

Salin tiga lajur nombor kad, orang dan tarikh ke kedudukan yang sepadan di sebelah kanan , dan kemudian gunakan Fungsi "Padam Pendua":

Selepas mengklik OK, kandungan pendua akan dipadamkan dan hanya satu baris bagi setiap orang akan dikekalkan setiap hari:

Seterusnya Tugasan adalah untuk mengisi masa tebuk masuk yang sepadan ke dalam kedudukan yang sepadan, dan tidak memaparkan masa khusus untuk data tidak normal, hanya memaparkan perkataan "tidak normal". Untuk mencapai tujuan ini, dua formula perlu digunakan. Mari kita lihat formula pertama dahulu.

2. Gunakan formula untuk mencatat masa masuk jam

Untuk memudahkan statistik masa jam masuk, anda mesti mula-mula buat nota mengikut masa berulang-alik, dan laksanakan yang berikut Kesan dalam gambar:

Ia dibahagikan kepada empat situasi mengikut masa berulang-alik:

1. Daftar masuk sebelum pukul 8 dikira bekerja pada waktu pagi

2 pada sebelah petang, secara artifisial ditetapkan bahawa mendaftar masuk antara 12:00 dan 12:30 dianggap sebagai keluar dari kerja pada waktu pagi

3 Dengan cara yang sama, Ia ditetapkan secara artifisial di antaranya 13:00 dan 13:30 bermaksud pergi bekerja pada waktu tengah hari

4 masa lain tidak sah , memaparkan kosong. Formula sel

E2 ialah:

Dwiklik isian untuk mencapai kesan dalam gambar.

=IF(D2=$L,D2=$P,D2=$N,$N,"")

JIKA pertama ialah

: =IF(D2Apabila d2 (masa daftar masuk) kurang daripada atau sama dengan k1 (waktu kerja pagi), hasil daripada fungsi if ialah k2 ( (Empat perkataan: pergi kerja pada waktu pagi), jika tidak, kembalikan nilai nol;

Jika kedua ialah

: IF(AND(D2>=$L,D2

apabila d2 (masa masa yang ditentukan) lebih besar daripada atau sama dengan L2 (memasukkan masa pada waktu pagi) dan kurang daripada atau sama dengan o1 (masa yang dinyatakan secara buatan), hasil daripada jika fungsi ialah L2 (empat perkataan "berhenti pada waktu pagi"), jika tidak, nilai nol dikembalikan.

Jika ketiga ialah

: IF(AND(D2>=$P,D2

Apabila d2 (masa pukulan) lebih besar daripada atau sama dengan p2 (masa mula jam masuk yang dinyatakan secara buatan) dan kurang daripada atau sama dengan M1 (waktu kerja petang), hasil fungsi if ialah M2 (empat perkataan "tidak bekerja pada sebelah petang"), jika tidak, nilai nol dikembalikan .

Jika yang keempat ialah : IF(D2>=$N,$N,"")

Apabila d2 (masa daftar masuk) lebih besar daripada atau sama dengan N1 (waktu tidak bertugas petang), hasil daripada fungsi if ialah N2 ( Empat perkataan (berhenti kerja pada sebelah petang), jika tidak, nilai nol dikembalikan.

Selepas melengkapkan maklumat catatan, tiba masanya untuk mengisi masa yang sepadan di kawasan yang sepadan Pada masa ini, anda boleh menggunakan formula untuk klik kanan dan tarik ke bawah untuk melengkapkan pengisian masa. Mari kita lihat formula ajaib.

3. Masa pengisian

Masukkan formula dalam sel K2:

=TEXT(SUMIFS($D:$D,$C:$C,$J3,$A:$A,$H3,$E:$E,K),"hh:mm:ss;;异常;")

Tarik ke bawah ke kanan untuk melengkapkan pengisian masa.

Formula ini menggunakan dua fungsi, teks dan sumif. Mari kita lihat prinsip formula.

Struktur fungsi sumifs ialah sumifs（要求和的数据区域，条件区域1，条件1，条件区域2，条件2……） Dalam contoh hari ini, kami menggunakan tiga syarat Jumlah sebenar ialah lajur D. Tiga syarat ialah tarikh, nombor kad dan maklumat catatan, yang memenuhi tiga syarat. Nombor dalam syarat semuanya unik, jadi hasil penjumlahan dan hasil rujukan adalah konsisten.

Jadi formulanya ialah:

SUMIFS($D:$D,$C:$C,$J3,$A:$A,$H3,$E:$E,K)

Menggunakan formula untuk mendapatkan keputusan adalah beberapa nombor seperti ini, kerana dalam Excel, tarikh Intipati masa dan masa ialah nombor Integer mewakili tarikh, dan perpuluhan mewakili masa Tukar format sel kawasan di atas kepada masa dan lihat kesannya, seperti yang ditunjukkan di bawah:

Malah, selepas menetapkan format sel, tujuan pada dasarnya tercapai Untuk meningkatkan kesan paparan dan memaksa paparan berada dalam format masa, kami menambah fungsi teks di luar sumif , walaupun dalam format biasa, ia juga dipaparkan mengikut masa, dan kedudukan 0 dipaparkan sebagai pengecualian.

Terangkan secara ringkas penggunaan teks, teks (data, format yang ditentukan), dalam contoh ini, format parameter kedua ditakrifkan sebagai mod paparan jam, minit dan saat , huruf h , m dan s masing-masing mewakili jam, minit dan saat, kedua-duanya dipaparkan dengan dua digit.

Koma bertitik dalam kod format boleh menetapkan mod paparan secara berasingan mengikut jenis data menyatakan bahawa data dibahagikan kepada empat jenis: nombor negatif; Dalam contoh ini, nombor positif dipaparkan mengikut format masa, nombor negatif dan teks tidak dipaparkan tanpa format yang ditentukan, dan sifar dipaparkan sebagai perkataan "tidak normal".

Bagi fungsi teks, hari ini anda hanya perlu memahami penggunaannya dalam contoh ini.

Ringkasan:

1. Penggunaan lajur tambahan yang munasabah: Penyusunan data kehadiran sentiasa menjadi masalah yang menyusahkan, dan selalunya. sangat sukar untuk membetulkannya dalam satu langkah , pada masa ini, penggunaan lajur tambahan yang munasabah boleh mengurangkan kesukaran masalah dengan banyak.

2. Formula ini tidak berkuasa: data asas kehadiran adalah masa, dan peraturan yang berkaitan bersama-sama membentuk hubungan logik antara data tersebut kes, ia tidak boleh dicapai melalui kaedah ini Ia juga mesti dipertimbangkan secara menyeluruh berdasarkan masa perjalanan yang sepadan dengan syif setiap orang, dan perisian sokongan mesin kehadiran diperlukan untuk melengkapkan ringkasan statistik data.

Cadangan pembelajaran berkaitan: tutorial cemerlang

Atas ialah kandungan terperinci Berkongsi kemahiran Excel praktikal: susun data kehadiran dengan cepat!. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!