Operasi dengan masa dalam Excel

Dalam kerja profesional dengan hamparan, adalah perkara biasa untuk berinteraksi dengan tarikh dan masa. Anda tidak akan dapat melakukannya tanpanya. Oleh itu, Tuhan sendiri memerintahkan untuk belajar bagaimana untuk bekerja dengan data jenis ini. Ini akan menjimatkan banyak masa dan mengelakkan banyak kesilapan semasa bekerja dengan hamparan.

Malangnya, ramai pemula tidak tahu bagaimana data diproses. Oleh itu, sebelum mempertimbangkan kelas operasi ini, adalah perlu untuk menjalankan program pendidikan yang lebih terperinci.

Cara tarikh diwakili dalam Excel

Maklumat tarikh diproses sebagai bilangan hari sejak 0 Januari 1900. Ya, anda tidak silap. Memang dari sifar. Tetapi ini perlu supaya ada titik permulaan, supaya 1 Januari sudah dianggap nombor 1, dan seterusnya. Nilai tarikh maksimum yang disokong ialah 2958465, yang seterusnya ialah 31 Disember 9999.

Kaedah ini membolehkan anda menggunakan tarikh untuk pengiraan dan formula. Jadi, Excel memungkinkan untuk menentukan bilangan hari antara tarikh. Skim ini mudah: yang kedua ditolak daripada satu nombor, dan kemudian nilai yang terhasil ditukar kepada format tarikh.

Untuk lebih jelas, berikut ialah jadual yang menunjukkan tarikh dengan nilai berangka yang sepadan.

Untuk menentukan bilangan hari yang telah berlalu dari tarikh A hingga tarikh B, anda perlu menolak yang pertama daripada yang terakhir. Dalam kes kami, ini adalah formulanya =B3-B2. Selepas memasukkannya, hasilnya adalah seperti berikut.

Adalah penting untuk ambil perhatian bahawa nilai adalah dalam hari kerana kami telah memilih format yang berbeza untuk sel daripada tarikh. Jika pada mulanya kami telah memilih format "Tarikh", maka hasilnya akan menjadi ini.

Adalah penting untuk memberi perhatian kepada titik ini dalam pengiraan anda.

Iaitu, untuk memaparkan nombor siri yang betul yang sepadan sepenuhnya dengan tarikh, anda mesti menggunakan sebarang format selain daripada tarikh. Sebaliknya, untuk menukar nombor menjadi tarikh, anda harus menetapkan format yang sesuai. 

Cara masa diwakili dalam Excel

Cara masa diwakili dalam Excel berbeza sedikit daripada tarikh. Hari diambil sebagai asas, dan jam, minit, saat adalah bahagian pecahannya. Iaitu, 24 jam ialah 1, dan mana-mana nilai yang lebih kecil dianggap sebagai pecahannya. Jadi, 1 jam ialah 1/24 hari, 1 minit ialah 1/1140, dan 1 saat ialah 1/86400. Unit masa terkecil yang tersedia dalam Excel ialah 1 milisaat.

Sama seperti tarikh, cara perwakilan ini memungkinkan untuk melakukan pengiraan dengan masa. Benar, ada satu perkara yang menyusahkan di sini. Selepas pengiraan, kami mendapat sebahagian daripada hari itu, bukan bilangan hari.

Tangkapan skrin menunjukkan nilai dalam format berangka dan format "Masa".

Kaedah pengiraan masa adalah serupa dengan tarikh. Ia adalah perlu untuk menolak masa yang lebih awal daripada masa yang kemudian. Dalam kes kami, ini adalah formulanya =B3-B2.

Oleh kerana sel B4 mula-mula mempunyai format Umum, maka pada penghujung pengenalan formula, ia serta-merta bertukar kepada "Masa". 

Excel, apabila bekerja dengan masa, melakukan operasi aritmetik biasa dengan nombor, yang kemudiannya diterjemahkan ke dalam format masa yang biasa kepada kita. 

Format tarikh dan masa

Setakat yang kami tahu, tarikh dan masa boleh disimpan dalam format yang berbeza. Oleh itu, anda perlu tahu cara memasukkannya dengan betul supaya pemformatannya betul. 

Sudah tentu, anda boleh menggunakan nombor siri hari atau sebahagian daripada hari apabila memasukkan tarikh dan masa, tetapi pendekatan ini sangat menyusahkan. Di samping itu, anda perlu sentiasa menggunakan format tertentu pada sel, yang hanya meningkatkan ketidakselesaan.

Oleh itu, Excel membolehkan anda menentukan masa dan tarikh dengan cara yang berbeza. Jika anda menggunakan salah satu daripadanya, maka program segera menukar maklumat ke dalam nombor yang sesuai dan menggunakan format yang betul ke sel.

Lihat jadual di bawah untuk senarai kaedah input tarikh dan masa yang disokong oleh Excel. Lajur kiri menyenaraikan format yang mungkin, dan lajur kanan menunjukkan cara ia akan dipaparkan dalam Excel selepas penukaran. Adalah penting untuk ambil perhatian bahawa jika tahun tidak ditentukan, tahun semasa, yang ditetapkan dalam sistem pengendalian, ditetapkan secara automatik.

Malah, terdapat lebih banyak cara untuk dipaparkan. Tetapi ini sudah cukup. Juga, pilihan rakaman tarikh tertentu mungkin berbeza bergantung pada negara atau rantau, serta tetapan sistem pengendalian.

Pemformatan Tersuai

Semasa bekerja dengan sel, pengguna boleh menentukan formatnya. Dia boleh membuatnya supaya hanya masa, bulan, hari, dan sebagainya dipaparkan. Ia juga mungkin untuk melaraskan susunan tarikh dirumuskan, serta pemisah.

Untuk mengakses tetingkap pengeditan, anda perlu membuka tab "Nombor", di mana anda boleh mencari pilihan tetingkap "Format Sel". Dalam kotak dialog yang terbuka, akan terdapat kategori "Tarikh" di mana anda boleh memilih format tarikh yang betul.

Jika anda memilih kategori "Masa", maka, dengan itu, senarai dengan pilihan untuk memaparkan masa akan muncul.

Untuk menggunakan pilihan pemformatan tertentu pada sel, anda mesti memilih format yang dikehendaki dan klik OK. Selepas itu, hasilnya akan digunakan. Jika tidak terdapat cukup format yang Excel tawarkan, maka anda boleh mencari kategori "Semua format". Terdapat banyak pilihan di luar sana juga.

Jika tiada pilihan yang sesuai, maka ia sentiasa mungkin untuk mencipta pilihan anda sendiri. Ia sangat mudah untuk melakukan ini. Anda hanya perlu memilih format pratetap sebagai sampel dan ikuti langkah berikut:

1. Pilih sel yang formatnya ingin anda ubah.
2. Buka kotak dialog "Format Sel" dan cari tab "Nombor".
3. Seterusnya, kategori "Semua format" dibuka, di mana kita dapati medan input "TYPE". Di sana anda perlu menentukan kod format nombor. Selepas anda memasukkannya, klik "OK".
4. Selepas langkah ini, sel akan memaparkan maklumat tarikh dan masa dalam format tersuai.

Menggunakan fungsi dengan tarikh dan masa

Apabila bekerja dengan tarikh dan masa, pengguna boleh menggunakan lebih daripada 20 fungsi berbeza. Dan walaupun jumlah ini mungkin terlalu banyak untuk seseorang, mereka semua boleh digunakan untuk mencapai matlamat tertentu.

Untuk mengakses semua fungsi yang mungkin, anda mesti pergi ke kategori "Tarikh dan Masa" kumpulan "Perpustakaan Fungsi". Kami akan mempertimbangkan hanya beberapa fungsi utama yang memungkinkan untuk mengekstrak pelbagai parameter daripada tarikh dan masa.

TAHUN ()

Menyediakan keupayaan untuk mendapatkan tahun yang sepadan dengan tarikh tertentu. Seperti yang anda sedia maklum, nilai ini boleh berada di antara 1900 dan 9999.

Sel 1 menunjukkan tarikh dalam format DDDD DD.MM.YYYY hh:mm:ss. Ini adalah format yang kami buat sebelum ini. Mari kita ambil sebagai contoh formula yang menentukan berapa tahun telah berlalu antara dua tarikh.

Pada masa yang sama, jika anda melihat lebih dekat, ternyata fungsi itu tidak mengira hasil yang betul sepenuhnya. Sebabnya ia hanya menggunakan tarikh dalam pengiraannya.

BULAN ()

Dengan fungsi ini, anda boleh menyerlahkan nombor bulan yang sepadan dengan tarikh tertentu. Mengembalikan keputusan antara 1 hingga 12. Nombor ini pula sepadan dengan nombor bulan.

HARI()

Sama seperti fungsi sebelumnya, fungsi ini mengembalikan bilangan hari dalam tarikh tertentu. Hasil pengiraan boleh berkisar antara 1 hingga 31.

MASA()

Seperti namanya, fungsi ini mengembalikan nombor jam, yang berjulat dari 0 hingga 23.

MINIT()

Fungsi yang mengembalikan bilangan minit dalam sel tertentu. Nilai yang mungkin dikembalikan adalah dari 0 hingga 59.

SECONDS()

Fungsi ini mengembalikan nilai yang sama seperti yang sebelumnya, kecuali ia mengembalikan saat.

HARI()

Dengan fungsi ini, anda boleh mengetahui nombor hari dalam seminggu yang digunakan pada tarikh ini. Nilai yang mungkin adalah dari 1 hingga 7, tetapi perlu diingat bahawa kira detik bermula dari Ahad, bukan Isnin, seperti yang biasa kita lakukan.

Walau bagaimanapun, menggunakan hujah kedua, fungsi ini membolehkan anda menyesuaikan format. Contohnya, jika anda melepasi nilai 2 sebagai parameter kedua, anda boleh menetapkan format supaya nombor 1 bermaksud hari Isnin dan bukannya Ahad. Ini lebih mudah untuk pengguna domestik.

Jika kita menulis 2 dalam hujah kedua, maka dalam kes kita fungsi akan mengembalikan nilai 6, yang sepadan dengan hari Sabtu.

HARI INI()

Fungsi ini sangat mudah: tiada hujah diperlukan untuk berfungsi. Ia mengembalikan nombor siri tarikh yang ditetapkan pada komputer. Jika ia digunakan pada sel yang format Umumnya ditetapkan, maka ia secara automatik akan ditukar kepada format "Tarikh".

TATA ()

Fungsi ini juga tidak memerlukan sebarang hujah. Ia berfungsi dengan cara yang sama seperti yang sebelumnya, hanya dengan tarikh dan masa. Ia digunakan jika perlu untuk memasukkan ke dalam sel tarikh dan masa semasa yang ditetapkan dalam komputer. Dan sama seperti dalam fungsi sebelumnya, apabila menggunakan yang ini, sel ditukar secara automatik kepada format tarikh dan masa, dengan syarat format "Umum" telah ditetapkan sebelum ini.

Kedua-dua fungsi sebelumnya dan fungsi ini ditukar secara automatik setiap kali helaian dikira semula, membolehkan untuk memaparkan masa dan tarikh yang paling terkini. 

Sebagai contoh, formula sedemikian boleh menentukan masa semasa.

=HARI INI()-HARI INI() 

Dalam kes ini, formula akan menentukan pecahan hari dalam format perpuluhan. Benar, anda perlu menggunakan format masa pada sel di mana formula ditulis, jika anda ingin memaparkan masa dengan tepat, dan bukan nombornya.

TARIKH()

Fungsi ini mempunyai tiga argumen, setiap satunya mesti dimasukkan. Selepas pengiraan, fungsi ini mengembalikan nombor siri tarikh. Sel ditukar secara automatik kepada format "Tarikh" jika ia mempunyai format "Umum" sebelum ini.

Hujah Hari atau Bulan boleh sama ada positif atau negatif. Dalam kes pertama, tarikh meningkat, dan dalam kes kedua, ia berkurangan.

Anda juga boleh menggunakan operasi matematik dalam hujah fungsi DATE. Sebagai contoh, formula ini menambah 1 tahun 5 bulan dan 17 hari kepada tarikh dalam sel A1.

Dan formula sedemikian memungkinkan untuk menukar rentetan teks menjadi tarikh kerja sepenuhnya, yang boleh digunakan dalam fungsi lain.

MASA()

Sama seperti fungsinya TARIKH(), fungsi ini mempunyai tiga parameter yang diperlukan - jam, minit dan saat. Selepas menggunakannya, nombor perpuluhan akan muncul dalam sel yang terhasil, tetapi sel itu sendiri akan diformatkan dalam format "Masa" jika ia mempunyai format "Umum" sebelum ini.

Dengan prinsip operasinya, fungsi MASA() и TARIKH() banyak perkara yang serupa. Oleh itu, tidak masuk akal untuk memberi tumpuan kepadanya. 

Adalah penting untuk ambil perhatian bahawa fungsi ini tidak boleh mengembalikan masa yang lebih besar daripada 23:59:59. Jika hasilnya lebih besar daripada ini, fungsi akan ditetapkan semula secara automatik kepada sifar.

fungsi TARIKH() и MASA() boleh diaplikasikan bersama.

Dalam tangkapan skrin ini, sel D1, yang menggunakan kedua-dua fungsi ini, mempunyai format masa tarikh. 

Fungsi Pengiraan Tarikh dan Masa

Secara keseluruhannya terdapat 4 fungsi yang membolehkan anda melakukan operasi matematik dengan tarikh dan masa.

DATAMES()

Menggunakan fungsi ini, anda boleh mengetahui nombor ordinal tarikh yang berada di belakang bilangan bulan yang diketahui (atau mendahului tarikh tertentu). Fungsi ini mengambil dua hujah: tarikh mula dan bilangan bulan. Hujah kedua boleh sama ada positif atau negatif. Pilihan pertama mesti dinyatakan jika anda ingin mengira tarikh masa depan, dan yang kedua - jika yang sebelumnya.

EOMONTH()

Fungsi ini memungkinkan untuk menentukan nombor ordinal hari terakhir bulan yang ketinggalan atau lebih awal daripada tarikh tertentu. Mempunyai hujah yang sama seperti yang sebelumnya.

HARI KERJA()

Sama seperti fungsi DATAMES(), hanya kelewatan atau pendahuluan berlaku pada bilangan hari bekerja tertentu. Sintaksnya serupa.

Ketiga-tiga fungsi ini mengembalikan nombor. Untuk melihat tarikh, anda perlu menukar sel kepada format yang sesuai. 

CLEAR()

Fungsi mudah ini menentukan bilangan hari perniagaan antara tarikh 1 dan tarikh 2.