Soalan lain dari seminar Atlanta Power Excel saya:
Bagaimanakah anda dapat melancarkan masa yang dapat ditagih hingga suku jam berikutnya?
Katakan bahawa anda menyimpan catatan masa yang dapat anda bayar. Anda mempunyai masa mula dan waktu tamat. Formula seperti =E2-D2akan mengira masa yang berlalu.
Hasil dalam F2 adalah 55 minit. Tetapi pemformatan lalai menunjukkan 12:55 AM. Pilih keseluruhan lajur. Tekan Ctrl + 1 untuk Memformat Sel. Pilih kategori Masa. Yang mana yang harus anda gunakan? Buat masa ini, pilih 13:30:55, tetapi kemudian anda akan melihat masalah dengan pilihan ini.
Dasar syarikat anda adalah untuk menyelesaikan semua pengisian sehingga suku jam berikutnya. Lakukan pengiraan ini: Terdapat 24 jam dalam sehari. Terdapat 4 suku jam dalam satu jam. Gandakan 24 * 4 untuk mendapat 96. Itu bermaksud seperempat jam bersamaan dengan 1/96 sehari.
Gunakan formula =CEILING(F2,1/96)untuk membulatkan masa yang berlalu dalam F hingga suku jam berikutnya. Dalam kaedah penyimpanan masa Excel, Noon adalah 0.5. 6AM ialah 0.25. 12:15 AM adalah 1/96 atau 0.010417. Lebih mudah untuk mengingati 1/96 daripada 0.010417.
Hasilnya ditunjukkan di bawah. Perhatikan bagaimana putaran 35 minit hingga 45 minit. Ini nampaknya formula yang digunakan oleh peguam saya… haha, itu hanya jenaka Esquire Dewey! Jangan menuntut saya! Saya tahu mereka menggunakan =CEILING(F2,4)…
Berhenti sebentar di sini … artikel ini semakin meningkat hingga suku jam berikutnya kerana itulah kaedah yang digunakan Larry dalam seminar saya. Ada peluang yang baik agar syarikat anda berpindah ke perkara lain. Gambar di bawah menunjukkan cara membundarkan hingga lima minit atau 1 minit. Anda boleh menggunakan logik yang serupa untuk membundarkan 6 minit terdekat (1/240) atau 12 minit (1/120).
Juga - cadangan lembut yang menunjukkan perbezaan antara FLOOR, MROUND, dan CEILING. Fungsi FLOOR di B9: B22 selalu membundar. MROUND dalam C9: C22 berpusing ke yang terdekat. CEILING di D9: D22 membundarkan.
Catatan
Petua alat Excel akan mencadangkan agar anda menggunakan FLOOR.MATH atau CEILING.MATH dan bukannya FLOOR atau CEILING. Versi ".Math" baru fungsi ini menangani nombor negatif secara berbeza. Oleh kerana masa di Excel tidak boleh menjadi negatif, tidak mengapa menggunakan Lantai atau LANGKAH biasa.
Tukar pecahan dari 1/96 hingga 1/288 kepada kenaikan pusingan hingga lima minit.
Menukar Masa ke Jam
Cadangan lain dari Larry: Tukar masa menjadi jam sebenar. Dengan cara itu, apabila anda mengalikan dengan kadar penagihan setiap jam, matematik akan berfungsi. Luangkan masa berlalu bulat dan kalikan dengan 24 untuk mendapatkan nombor perpuluhan jam. Pastikan untuk memformat hasilnya sebagai angka dengan dua tempat perpuluhan dan bukan satu masa.
Format Masa Mana Yang Akan Digunakan?
Sudah tiba masanya untuk membincangkan pilihan pemformatan masa tersebut: Anda mungkin telah memilih 13:30, 13:30:55, atau 37:30:55. Dalam tangkapan skrin di atas, saya telah menggunakan 13:30:55. Masalah dengan ini muncul pada akhir minggu. Apabila anda berjumlah jam perpuluhan di lajur H, anda mempunyai 53 jam yang boleh dikenakan. Apabila anda mengira waktu dalam G, anda mendapat lima jam. Apa yang sedang berlaku?
Kedua-duanya betul, jika anda memahami format nombor yang anda pilih. Katakan bahawa anda memasukkan =NOW()dalam sel. Anda boleh memformat sel untuk menunjukkan tarikh & waktu, tarikh, atau waktu sahaja. Excel tidak mempunyai masalah untuk meninggalkan bahagian 29 Mei pada 29 Mei 08:15:34 pagi jika anda menyuruhnya melakukannya. Jawapan dalam sel G57 adalah "2 hari dan lima jam". Tetapi apabila anda memilih 13:37:55 sebagai format waktu, anda menyuruh Excel untuk meninggalkan bilangan hari dan hanya menunjukkan jam, minit, dan saat kepada anda.
Sekiranya anda menukar lajur G untuk menggunakan format 37:30:55, anda mendapat bilangan jam yang betul:
Kapsyen di atas gambar itu menangkap masalah kecil dengan format 37:30:55: ia menunjukkan detik, yang nampaknya tidak perlu kerana kita membundarkan diri ke lima belas minit terdekat. Setelah memilih 37:30:55, klik pada kategori Custom. Anda dapat melihat kod pemformatan nombor yang (h):mm:ss;@digunakan. Kurungan segi empat di sekitar H adalah bahagian penting.
Untuk menunjukkan waktu anda dalam jam dan minit, edit format nombor untuk membuang: ss.
Tonton Video
Video hari ini mengandungi tip bonus di akhir untuk memformat perpuluhan sebagai pecahan.
Transkrip Video
Belajar Excel Dari Podcast, Episod 2210: Bundar ke Jam Suku Akhir.
Hei, selamat datang kembali ke netcast, saya Bill Jelen. Hari ini, satu lagi soalan dari seminar Atlanta Power Excel saya. Seseorang melakukan spreadsheet penagihan, dan mereka mahu membundarkan hingga suku jam berikutnya, bukan? Jadi, untuk setiap tugas di sini, mereka mempunyai Pelanggan, Waktu Mula, dan Waktu Akhir, dan untuk mengira Masa yang Berlalu - itu mudah, jika itu adalah masa nyata, atau jika mereka hanya = E2-D2. Jadi, itu mengatakan 55 minit, tetapi ia mengatakannya dengan cara yang sangat pelik-- "12:55 AM." Kami mahu terus maju dan memformatnya. Pilih Masa, dan hei, lihat, ada beberapa pilihan yang berbeza di sini: 13:30; 13:30:55, yang akan menunjukkan detik; dan kemudian 37:30:55. Sekarang, buat masa ini, saya akan memilih jam 13:30, tetapi kita akan melingkari kemudian dan mengkaji semula pilihan itu. Oleh itu, saya memilih OK dan klik dua kali untuk menyalinnya,dan anda melihat bahawa ia hanya melakukan matematik - jadi di sini, 25 minit. Tetapi, orang yang mengemukakan soalan ini - firma tempat mereka bekerja - mereka mahu mengumpulkan sehingga suku jam berikutnya.
Baiklah, jadi, jam seperempat. Mari fikirkan masa di Excel. Tengah hari ialah 0.5. Sekiranya saya memformatnya mengikut masa-- 12:00:00 PM. Dan jika saya melakukan 0.25, itu akan menjadi 6:00:00 pagi. Baiklah, jadi cara ini berfungsi ialah satu hari penuh adalah nombor 1.0, dan jadi, jika kita ingin mengetahui 15 minit, itu akan menjadi - ada 24 jam x 4 suku jam, jadi kita harus meletakkan = 1/96 di sana, dan itu memberi kita 15 minit. Sekarang, 1/96 - Saya boleh membuat matematik seperti itu di kepala saya: 24x4; 1/96 - pastinya lebih mudah untuk mengingati 1/96 daripada mengingat perpuluhan ini: 0.01041666667. Baiklah, jadi lebih senang menggunakan 1/96 di sana, dan jadi kita akan menggunakan fungsi yang disebut CEILING.
PEMILIHAN. Lihat, kami mempunyai pilihan antara CEILING dan CEILING.MATH untuk nombor positif yang serupa. Lebih pendek untuk mengetik CEILING, jadi saya akan menggunakan CEILING. Dan, bulat, ada 1/96 sehari - iaitu 15 minit-- dan itulah jawapan yang tepat, dalam format yang salah. Kami akan menyalin format ini lebih - Ctrl + C, dan kemudian Alt + E + S + T untuk menampal format khas - dan pusingan 55 minit hingga jam berikutnya. Klik dua kali untuk menyalinnya, dan, lihat, putaran 35 minit hingga 45 seterusnya; 25 pusingan hingga 30 seterusnya. Nampaknya ia berfungsi, walaupun orang yang menanyakan soalan ini - mereka membundarkan hingga 15 yang berikutnya. Mungkin juga syarikat anda menggunakan sesuatu yang berbeza.
Baiklah, jadi untuk pusingan ke suku jam terdekat, kami menggunakan 1/96. Tetapi untuk pusingan ke 5 minit terdekat - ada 24 jam, ada 12 tempoh 5 minit dalam setiap jam, jadi 12x4 = 288 Gunakan 1/288 untuk pusingan hingga 5 minit terdekat. Sekiranya anda melangkah ke 6 minit terdekat - terdapat 10 jangka masa 6 minit x 24 jam; anda membundarkan ke 1/240 terdekat. Untuk pusingan ke minit terdekat, pilih ke 1/1440 terdekat.
Baiklah, jadi anda boleh melakukan ini, dan kami juga mempunyai pilihan. CEILING akan sentiasa bulat, jadi 16 minit - mari pergi ke 1/96 di sana - 16 minit akan meningkat hingga 30, dan, walaupun saya yakin peguam saya berpendapat bahawa itu adalah kaedah yang baik untuk membuat pembayaran, ia tidak benar-benar adil kepada saya-- pelanggan. Baiklah, jadi anda tahu CEILING akan membulat. Peguam saya tidak akan pernah menggunakan ini: Lantai ini akan sentiasa membundar Tetapi, mungkin apa yang paling adil di sini adalah menggunakan MROUND. Oleh itu, = PEKERJAAN Masa hingga 1/96 yang terdekat, dan itu akan menjadi lebih kurang sehingga kira-kira 22.5 minit; dan kemudian, selepas itu, ia akan menjadi bulat. Baiklah, jadi ia melengkung ke apa sahaja yang paling dekat dengannya. Oleh itu, anda harus memilih sama ada anda akan menggunakan Lantai, MROUND, atau CEILING, dan kemudian apa yang akan menjadi pembahagi anda - sama ada 's akan menjadi 5 minit terdekat, minit terdekat, atau, anda tahu, apa sahaja. Baiklah, jadi di sana kita mengumpul.
Sekarang, petua bonus di sini - ini adalah Larry dalam seminar itu - Larry berkata, "Hei, lihat, tidak bagus untuk menunjukkan waktu yang dapat ditagih pada masa seperti itu; kami benar-benar ingin menukar menjadi jam kerana anda benar-benar akan pergi untuk menagih pada kadar tertentu - anda tahu, $ 35 / jam, $ 100 / jam, $ 500 / jam, berapa pun kadar anda - dan kita harus dapat menggandakan jam x kadarnya. Oleh itu, eloklah dapat menukar jam tersebut menjadi waktu perpuluhan. Oleh itu, kita mengambil masa itu dalam G2 dan mengalikannya dengan 24, dan itu akan memberi kita bilangan perpuluhan jam, tetapi akan berada dalam format yang salah. akan memilih format waktu dalam kes ini, apabila tidak seharusnya format masa sama sekali; mestilah dalam format Nombor. Dan pastikan untuk memasukkan sekurang-kurangnya 2 tempat perpuluhan. Klik dua kali untuk menyalinnya, dan kita mendapat masa kita. "
Baiklah, sekarang, kembali ke soalan kami di sini mengenai "Format Nombor mana yang harus kita gunakan?", Ini adalah tempat yang sangat baik untuk dibincangkan mengenai "Mengapa?". Sekiranya kita memilih semua nombor ini, di sini, kita akan melihat bahawa jumlahnya adalah 53 jam, dan jika saya datang ke sini dan memasukkan fungsi SUM, kita mendapat 53 jam. Itu sempurna. Tetapi, jika saya menggunakan fungsi SUM di bawah lajur waktu, dan saya melakukan Alt + = atau AUTOSUM, ia memberi saya 5 jam… 5 jam. Yang manakah betul - 53 atau 5?
Nah, ternyata dalam arti pelik, mereka berdua betul. Tetapi, inilah sebabnya mereka berdua betul. Saya akan memasukkan fungsi yang dipanggil = SEKARANG. Anda akan melihat bahawa saya sebenarnya merakamnya kembali pada 9 Mei, jadi, 20 hari yang lalu, dan jam 7:08 pagi. Dan dengan = SEKARANG, kadang-kadang saya mahu menunjukkan tarikh dan waktu, tetapi pada waktu lain, saya hanya mahu untuk menunjukkan tarikh. Jadi, saya boleh buat = SEKARANG beri saya tarikh semasa, atau, saya boleh buat = SEKARANG beri saya masa sekarang. Oleh itu, fungsi Nombor dengan senang hati dapat mengurangkan waktu cuti, atau membuang tarikhnya. Dan ini yang kami pilih di sini, jam 13:30, mengatakan, "Hei, saya tidak mahu melihat Jumlah hari; tunjukkan saja waktunya." Ya, itulah masalahnya - kerana ini benar-benar 2 hari 5 jam - 2 hari adalah 48 jam-- 2 hari 5 jam. Dan kami mengatakannya - kami berkata kepada Excel, "Hei, jangan repot-repot menunjukkan hari-hari. "Baiklah, kami tidak tahu kami memberitahu Excel itu, tapi itulah yang kami katakan kepada Excel ketika kami memilih yang ini atau yang ini. Baiklah, jadi kamu terpaksa turun ke sini pada jam 37:30:55. Itulah yang akan menunjukkan kepada kita jumlah jam penuh, dan kita mendapat jawapan yang betul sebanyak 53 jam.
Tetapi, nampaknya sangat bodoh untuk menunjukkan detik-detik tambahan ketika kita membulatkan masa hingga 15 minit. Oleh itu, mari kita kembali ke perkara itu. Selepas anda memilih 37:30:55 untuk mengetahui format Nombor di sebalik ini, klik pada Custom. Anda akan melihat bahawa perkara yang membuatnya berfungsi adalah tanda kurung persegi sekitar jam. Tetapi, kami tidak memerlukan: ss. Jadi, sekarang kita dapat mengedit format khusus itu, dan kita mempunyai waktu dan minit. Kita tidak perlu memahami hari ini apa maksudnya @. Ia mengatakan bahawa, untuk teks, hanya tunjukkan teksnya; tetapi, tinggalkan bahagian itu di sana, walaupun anda tidak memahaminya. Klik OK, dan sekarang kita mempunyai masa dalam beberapa jam dan minit, meninggalkan beberapa saat. Tetapi, jumlahnya berjaya. Sungguh, sangat halus, bahawa 13:30 dan 13:30:55. Anda tahu, itu tidak mengatakan, "Hei, anda memberitahu Excel untuk mengabaikan tarikh … hari-hari penuh." Tetapi,Awak.
Perkara-perkara seperti Taking Time Math ada dalam buku ini: LIVe: 54 Petua Excel Paling Hebat Sepanjang Masa. Klik "I" di sudut kanan atas untuk melihatnya.
Ini terdengar seperti soalan yang mudah; Saya tidak percaya bahawa kita sudah 7 minit. Tetapi, sebuah syarikat ingin membuat pembayaran dalam suku jam, dan mereka selalu mahu meningkat hingga suku jam berikutnya. Jadi, 24 jam dalam sehari, 24x4 = 96; 15 minit = 1/96. Jadi, gunakan CEILING (A2,1 / 96) untuk membundarkan hingga suku jam berikutnya. Gunakan MROUND (A2,1 / 96) untuk pusingan ke suku jam terdekat, dan kemudian gunakan FLOOR (A2,1 / 96) untuk membundarkan ke suku suku yang lebih rendah - selalu kembali. Terdapat 24 jam dalam sehari, jadi anda dapat melakukan 1/288 untuk mencapai jangka masa 5 minit, 1/240 hingga jangka masa 6 minit, 1/1440 untuk pusingan ke minit terdekat. Dan kemudian, petua bonus ada di akhir (format Nombor mana) - anda selalu mahu kembali ke 37:30:55 jika anda akan menunjukkan jam melebihi 24 jam.
Untuk memuat turun buku kerja dari video hari ini, lawati URL tersebut di sana dalam keterangan Youtube.
Saya ingin mengucapkan terima kasih kerana berhenti; kami akan berjumpa anda di lain masa untuk siaran lain dari.
Hey! Terima kasih kerana berpaut. Tetapi, hanya satu lagi petua bonus di sini (yang tidak akan saya sebutkan) Apa yang saya mulakan di sini - mari kita jelaskan semua ini - formula kecil yang muncul dan menunjukkan salah satu perpuluhan ini - jadi, suku jam terdekat. Tetapi, saya ingin menunjukkan bahawa tidak seperti 0.010417. Hebatnya - dalam Pemformatan Nombor, sebenarnya ada yang disebut Pecahan, dan jika saya memilih "Hingga dua digit," saya akan mendapat 1/96; tetapi, saya perlu mengatasinya 1/288, jadi saya memilih "Hingga tiga digit", dan masih menunjukkan 1/96; tetapi, ketika saya menukar ke yang ini - 1/288 - ia akan menunjukkannya dengan betul. Sekarang, saya rasa saya seharusnya melakukan "empat digit" untuk menangani 1/1440. Apa yang akan dibuat? Itu akan memberi saya … Tetapi,jika saya masuk ke Format Nombor, dan pergi ke Custom, dan tambahkan tanda tanya tambahan di sana, maka kita akan sampai ke 1/1440 pecahan terdekat. Format Nombor - perkara yang menarik, sejuk.
Muat turun Fail Excel
Untuk memuat turun fail excel: round-to-quarter-hour.xlsx
Pemikiran Excel Hari Ini
Saya telah meminta nasihat rakan Excel saya mengenai Excel. Pemikiran hari ini untuk merenungkan:
"Kawan jangan biarkan rakan menggunakan LANGSUNG"
Jordan Goldmeier
