{"id":843,"date":"2024-12-02T02:56:06","date_gmt":"2024-12-02T02:56:06","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/chia-khoang-thoi-gian-de-lam-bai-toan-phan-tich-du-lieu-luot-truy-cap\/"},"modified":"2024-12-02T02:56:06","modified_gmt":"2024-12-02T02:56:06","slug":"chia-khoang-thoi-gian-de-lam-bai-toan-phan-tich-du-lieu-luot-truy-cap","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/chia-khoang-thoi-gian-de-lam-bai-toan-phan-tich-du-lieu-luot-truy-cap\/","title":{"rendered":"Chia kho\u1ea3ng th\u1eddi gian \u0111\u1ec3 l\u00e0m b\u00e0i to\u00e1n ph\u00e2n t\u00edch d\u1eef li\u1ec7u l\u01b0\u1ee3t truy c\u1eadp"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<div class=\"wp-block-columns are-vertically-aligned-center is-layout-flex wp-container-core-columns-layout-1 wp-block-columns-is-layout-flex\">\n<div class=\"wp-block-column is-vertically-aligned-center is-layout-flow wp-block-column-is-layout-flow\" style=\"flex-basis:100%\">\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"556\" height=\"498\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/image-14.png\" class=\"wp-image-40704\" title=\"image-14\"><\/figure>\n<\/div>\n<\/div>\n<\/div>\n<p>H\u00f4m n\u1ecd m\u00ecnh c\u00f3 t\u00ecnh c\u1edd \u0111\u1ecdc \u0111\u01b0\u1ee3c b\u00e0i vi\u1ebft trong \u1ea3nh n\u00e0y. Y\u00eau c\u1ea7u \u1edf \u0111\u00e2y l\u00e0 t\u1eeb d\u1eef li\u1ec7u d\u1ea1ng datetime (c\u00f3 c\u1ea3 ng\u00e0y th\u00e1ng n\u0103m v\u00e0 gi\u1edd ph\u00fat gi\u00e2y), t\u00ecm ra khung gi\u1edd n\u00e0o c\u00f3 nhi\u1ec1u l\u01b0\u1ee3t truy c\u1eadp nh\u1ea5t. \u0110\u00e2y l\u00e0 m\u1ed9t b\u00e0i to\u00e1n kh\u00e1 \u0111\u01a1n gi\u1ea3n nh\u01b0ng c\u0169ng v\u00f4 c\u00f9ng th\u1ef1c t\u1ebf v\u1edbi nhi\u1ec1u \u1ee9ng d\u1ee5ng, ch\u1eb3ng h\u1ea1n nh\u01b0 \u0111\u1ec3 ph\u00e2n t\u00edch th\u00f3i quen v\u00e0 h\u00e0nh vi ng\u01b0\u1eddi d\u00f9ng, hay x\u00e1c \u0111\u1ecbnh c\u00e1c k\u00eanh ti\u1ebfp c\u1eadn kh\u00e1ch h\u00e0ng h\u1ee3p l\u00fd. Trong b\u00e0i vi\u1ebft n\u00e0y,  H\u1ecdc Excel Online s\u1ebd h\u01b0\u1edbng d\u1eabn c\u00e1c b\u1ea1n c\u00e1ch chia kho\u1ea3ng th\u1eddi gian (c\u1ee5 th\u1ec3 l\u00e0 theo gi\u1edd) \u0111\u1ec3 b\u1eaft \u0111\u1ea7u ph\u00e2n t\u00edch d\u1eef li\u1ec7u.<\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_61 counter-hierarchy ez-toc-counter ez-toc-transparent ez-toc-container-direction\">\n<p class=\"ez-toc-title\">Xem nhanh<\/p>\n<p><label for=\"ez-toc-cssicon-toggle-item-674d21c506bd6\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><\/span><\/span><\/label><\/p>\n<nav>\n<ul class=\"ez-toc-list ez-toc-list-level-1 \">\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/blog.hocexcel.online\/chia-khoang-thoi-gian-excel.html#Du_lieu\" title=\"D\u1eef li\u1ec7u\">D\u1eef li\u1ec7u<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/blog.hocexcel.online\/chia-khoang-thoi-gian-excel.html#Tach_du_lieu_theo_ngaythangnam\" title=\"T\u00e1ch d\u1eef li\u1ec7u theo ng\u00e0y\/th\u00e1ng\/n\u0103m\">T\u00e1ch d\u1eef li\u1ec7u theo ng\u00e0y\/th\u00e1ng\/n\u0103m<\/a>\n<ul class=\"ez-toc-list-level-3\">\n<li class=\"ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/blog.hocexcel.online\/chia-khoang-thoi-gian-excel.html#Tinh_nang_Group_giup_tach_va_nhom_du_lieu_theo_y_muon\" title=\"T\u00ednh n\u0103ng Group gi\u00fap t\u00e1ch v\u00e0 nh\u00f3m d\u1eef li\u1ec7u theo \u00fd mu\u1ed1n\">T\u00ednh n\u0103ng Group gi\u00fap t\u00e1ch v\u00e0 nh\u00f3m d\u1eef li\u1ec7u theo \u00fd mu\u1ed1n<\/a><\/li>\n<\/ul>\n<\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/blog.hocexcel.online\/chia-khoang-thoi-gian-excel.html#Tu_tao_nhan_chia_khoang_thoi_gian_gio_phut_giay_trong_Excel\" title=\"T\u1ef1 t\u1ea1o nh\u00e3n chia kho\u1ea3ng th\u1eddi gian gi\u1edd, ph\u00fat, gi\u00e2y trong Excel\">T\u1ef1 t\u1ea1o nh\u00e3n chia kho\u1ea3ng th\u1eddi gian gi\u1edd, ph\u00fat, gi\u00e2y trong Excel<\/a>\n<ul class=\"ez-toc-list-level-3\">\n<li class=\"ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/blog.hocexcel.online\/chia-khoang-thoi-gian-excel.html#Lay_du_lieu_theo_khoang_thoi_gian_phut_giay\" title=\"L\u1ea5y d\u1eef li\u1ec7u theo kho\u1ea3ng th\u1eddi gian ph\u00fat, gi\u00e2y\">L\u1ea5y d\u1eef li\u1ec7u theo kho\u1ea3ng th\u1eddi gian ph\u00fat, gi\u00e2y<\/a><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Du_lieu\"><\/span>D\u1eef li\u1ec7u<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>D\u01b0\u1edbi \u0111\u00e2y l\u00e0 b\u1ea3ng d\u1eef li\u1ec7u m\u1eabu:<\/p>\n<figure class=\"wp-block-table\">\n<table>\n<tbody>\n<tr>\n<td>Time<\/td>\n<td>Event<\/td>\n<\/tr>\n<tr>\n<td>3\/3\/2023 15:13<\/td>\n<td>1<\/td>\n<\/tr>\n<tr>\n<td>3\/3\/2023 14:16<\/td>\n<td>1<\/td>\n<\/tr>\n<tr>\n<td>3\/3\/2023 14:15<\/td>\n<td>1<\/td>\n<\/tr>\n<tr>\n<td>2\/27\/2023 12:25<\/td>\n<td>1<\/td>\n<\/tr>\n<tr>\n<td>2\/27\/2023 10:57<\/td>\n<td>1<\/td>\n<\/tr>\n<tr>\n<td>2\/24\/2023 9:58<\/td>\n<td>1<\/td>\n<\/tr>\n<tr>\n<td>2\/21\/2023 8:47<\/td>\n<td>1<\/td>\n<\/tr>\n<tr>\n<td>2\/20\/2023 16:07<\/td>\n<td>1<\/td>\n<\/tr>\n<tr>\n<td>1\/16\/2023 7:49<\/td>\n<td>1<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/figure>\n<p>Gi\u1edd ta s\u1ebd ti\u1ebfn h\u00e0nh b\u00f3c t\u00e1ch d\u1eef li\u1ec7u.<\/p>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Tach_du_lieu_theo_ngaythangnam\"><\/span>T\u00e1ch d\u1eef li\u1ec7u theo ng\u00e0y\/th\u00e1ng\/n\u0103m<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>\u0110\u1ea7u ti\u00ean, ta c\u1ea7n ph\u1ea3i ch\u1eafc ch\u1eafc d\u1eef li\u1ec7u c\u1ee7a m\u00ecnh \u1edf d\u1ea1ng Date (ho\u1eb7c DateTime). M\u1ed9t c\u00e1ch \u0111\u1ec3 ki\u1ec3m tra trong Excel \u0111\u00f3 l\u00e0 s\u1eed d\u1ee5ng h\u00e0m ISNUMBER (trong Google Sheets, ta c\u00f3 th\u00eam h\u00e0m ISDATE)<\/p>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"311\" height=\"91\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/image-1-6.png\" class=\"wp-image-40705\" title=\"image-1-6\"><\/figure>\n<\/div>\n<p>Sau khi \u0111\u00e3 ch\u1eafc ch\u1eafn d\u1eef li\u1ec7u \u0111\u00fang chu\u1ea9n, ta ti\u1ebfn h\u00e0nh \u0111\u01b0a v\u00e0o Pivot Table b\u1eb1ng c\u00e1ch ch\u1ecdn to\u00e0n b\u1ed9 b\u1ea3ng v\u00e0 ch\u1ecdn <strong>Insert -&gt; Pivot Table -&gt; From Data\/Range<\/strong><\/p>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"254\" height=\"418\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/image-2-7.png\" class=\"wp-image-40706\" title=\"image-2-7\"><\/figure>\n<\/div>\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Tinh_nang_Group_giup_tach_va_nhom_du_lieu_theo_y_muon\"><\/span>T\u00ednh n\u0103ng Group gi\u00fap t\u00e1ch v\u00e0 nh\u00f3m d\u1eef li\u1ec7u theo \u00fd mu\u1ed1n<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"415\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/image-3-1024x415-2.png\" class=\"wp-image-40707\" title=\"image-3-1024x415-2\"><\/figure>\n<\/div>\n<p>Khi ta ti\u1ebfn h\u00e0nh \u0111\u01b0a d\u1eef li\u1ec7u v\u00e0o Pivot t\u01b0\u01a1ng \u1ee9ng, k\u1ebft qu\u1ea3 s\u1ebd ch\u01b0a th\u1ec3 hi\u1ec3n th\u1ecb ngay b\u1edfi d\u1eef li\u1ec7u c\u1ee7a t\u1eebng d\u00f2ng ho\u00e0n to\u00e0n kh\u00e1c nhau. Trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y, t\u00ednh n\u0103ng Group trong Pivot Table s\u1ebd l\u00e0 gi\u1ea3i ph\u00e1p.<\/p>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"702\" height=\"432\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/image-4-5.png\" class=\"wp-image-40708\" title=\"image-4-5\"><\/figure>\n<\/div>\n<p>\u0110\u1ec3 c\u00f3 th\u1ec3 truy c\u1eadp \u0111\u01b0\u1ee3c t\u00ednh n\u0103ng n\u00e0y, b\u01b0\u1edbc \u0111\u1ea7u ti\u00ean b\u1ea1n ph\u1ea3i <strong>ch\u1ecdn d\u1eef li\u1ec7u mu\u1ed1n group, <\/strong>b\u1edfi khi \u0111\u00f3 t\u00ednh n\u0103ng n\u00e0y m\u1edbi cho ph\u00e9p b\u1ea1n ch\u1ecdn. Trong tr\u01b0\u1eddng h\u1ee3p tr\u00ean, c\u00f3 hai c\u00e1ch. Ho\u1eb7c ta ch\u1ecdn <strong>to\u00e0n b\u1ed9<\/strong> c\u1ed9t Time trong Pivot Table, sau \u0111\u00f3 ch\u1ecdn t\u1edbi th\u1ebb PivotTable Analyze v\u00e0 ch\u1ecdn <strong>Group Selection<\/strong>. Ho\u1eb7c, ta ch\u1ecdn <strong>1 \u00f4<\/strong> trong c\u1ed9t Time, sau \u0111\u00f3 ch\u1ecdn <strong>Group Field <\/strong>(ch\u1ec9 khi ch\u1ecdn 1 \u00f4 m\u1edbi c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng Group Field).<\/p>\n<div class=\"code-block code-block-1\" style=\"margin: 8px 0;clear: both\">\n<div class=\"ad-inserterpro\">\n<a href=\"https:\/\/hocexcel.online\/course\/preview\/ex101-excel-tu-co-ban-den-chuyen-gia-danh-cho-nguoi-di-lam\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/ex101_92019-nho-49.gif\" title=\"ex101_92019-nho-49\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"431\" height=\"451\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/image-5-5.png\" class=\"wp-image-40709\" title=\"image-5-5\"><\/figure>\n<\/div>\n<p>Khi h\u1ed9p tho\u1ea1i <strong>Grouping<\/strong> hi\u1ec7n ra, c\u00f3 nh\u1eefng th\u00e0nh ph\u1ea7n sau:<\/p>\n<ul>\n<li>Starting at: Th\u1eddi \u0111i\u1ec3m b\u1eaft \u0111\u1ea7u chia nh\u00f3m. Ch\u1eb3ng h\u1ea1n, n\u1ebfu ta b\u1eaft \u0111\u1ea7u t\u1eeb ng\u00e0y 24 th\u00e1ng 2, t\u1ea5t c\u1ea3 nh\u1eefng ng\u00e0y tr\u01b0\u1edbc \u0111\u00f3 s\u1ebd \u0111\u01b0\u1ee3c gom v\u00e0o chung 1 nh\u00f3m v\u1edbi t\u00ean \u201c&lt;2\/21\/2023\u201d<\/li>\n<li>Ending at: Th\u1eddi \u0111i\u1ec3m k\u1ebft th\u00fac chia nh\u00f3m. T\u01b0\u01a1ng t\u1ef1 Starting at, nh\u1eefng ng\u00e0y sau ng\u00e0y ending s\u1ebd \u0111\u01b0\u1ee3c \u0111\u01b0a v\u00e0o 1 nh\u00f3m chung \u201c&gt;ending date\u201d<\/li>\n<li>By: X\u00e1c \u0111\u1ecbnh c\u00e1ch hi\u1ec3n th\u1ecb d\u1eef li\u1ec7u \u0111\u01b0\u1ee3c nh\u00f3m. Ch\u1eb3ng h\u1ea1n, n\u1ebfu nh\u00f3m theo th\u00e1ng, ta s\u1ebd c\u00f3 3 d\u00f2ng duy nh\u1ea5t l\u00e0 Th\u00e1ng 1, Th\u00e1ng 2 v\u00e0 Th\u00e1ng 3. B\u1ea1n c\u0169ng c\u00f3 th\u1ec3 ch\u1ecdn nhi\u1ec1u c\u00e1ch hi\u1ec3n th\u1ecb trong n\u00e0y, v\u00ed d\u1ee5 n\u1ebfu ch\u1ecdn Days v\u00e0 Months th\u00ec ta s\u1ebd c\u00f3 2 tr\u01b0\u1eddng d\u1eef li\u1ec7u m\u1edbi t\u1ef1 \u0111\u1ed9ng \u0111\u01b0\u1ee3c sinh ra trong b\u1ea3ng<\/li>\n<li>Number of days: V\u1edbi d\u1eef li\u1ec7u days, b\u1ea1n c\u00f3 th\u1ec3 t\u1ea1o c\u00e1c kho\u1ea3ng, vd nh\u01b0 3 ng\u00e0y, 5 ng\u00e0y, 10 ng\u00e0y.<\/li>\n<\/ul>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/image-6-5.png\" class=\"wp-image-40710\" width=\"357\" height=\"137\" title=\"image-6-5\"><figcaption class=\"wp-element-caption\">Tr\u01b0\u1eddng d\u1eef li\u1ec7u sinh ra khi ch\u1ecdn nhi\u1ec1u m\u1ee5c trong <strong>By<\/strong><\/figcaption><\/figure>\n<\/div>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"494\" height=\"313\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/image-7-5.png\" class=\"wp-image-40711\" title=\"image-7-5\"><figcaption class=\"wp-element-caption\">K\u1ebft qu\u1ea3 khi ch\u1ecdn Number of days = 5<\/figcaption><\/figure>\n<\/div>\n<p>T\u1edbi \u0111\u00e2y, ta \u0111\u00e3 \u0111\u1ea1t \u0111\u01b0\u1ee3c m\u1ee5c \u0111\u00edch ph\u00e2n chia theo kho\u1ea3ng ng\u00e0y. Tuy nhi\u00ean, n\u1ebfu ta mu\u1ed1n ph\u00e2n chia theo kho\u1ea3ng kh\u00e1c, ch\u1eb3ng h\u1ea1n nh\u01b0 3 gi\u1edd, 5 gi\u1edd, 8 gi\u1edd, 15 ph\u00fat, 30 ph\u00fat\u2026 th\u00ec sao? Khi m\u00e0 t\u00ednh n\u0103ng m\u1eb7c \u0111\u1ecbnh Group kh\u00f4ng th\u1ec3 gi\u00fap, ta s\u1ebd t\u1ef1 t\u1ea1o ra c\u00e1c nh\u00e3n th\u1eddi gian.<\/p>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Tu_tao_nhan_chia_khoang_thoi_gian_gio_phut_giay_trong_Excel\"><\/span>T\u1ef1 t\u1ea1o nh\u00e3n chia kho\u1ea3ng th\u1eddi gian gi\u1edd, ph\u00fat, gi\u00e2y trong Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>V\u1edbi b\u00e0i to\u00e1n c\u1ee5 th\u1ec3 theo d\u00f5i l\u01b0u l\u01b0\u1ee3ng truy c\u1eadp, ta c\u00f3 th\u1ebb b\u1ecf qua d\u1eef li\u1ec7u ng\u00e0y th\u00e1ng m\u00e0 ch\u1ec9 c\u1ea7n t\u1eadp trung t\u1edbi th\u1eddi gian. \u0110\u1ec3 tr\u1ea3 l\u1eddi cho c\u00e2u h\u1ecfi \u201ckhung gi\u1edd n\u00e0o c\u00f3 l\u01b0\u1ee3ng truy c\u1eadp l\u1edbn nh\u1ea5t trong su\u1ed1t th\u1eddi gian qua\u201d, ta s\u1ebd chia gi\u1edd trong ng\u00e0y ra nhi\u1ec1u ph\u1ea7n. \u1ede v\u00ed d\u1ee5 n\u00e0y, ta chia ra th\u1ed1ng k\u00ea d\u1eef li\u1ec7u c\u1ee7a m\u1ed7i 3 ti\u1ebfng.<\/p>\n<p>\u0110\u1ea7u ti\u00ean, ta t\u1ea1o th\u00eam 1 c\u1ed9t n\u1eefa \u0111\u1ec3 tr\u00edch xu\u1ea5t ri\u00eang gi\u1edd ra v\u1edbi c\u00f4ng th\u1ee9c:<\/p>\n<pre class=\"wp-block-code\"><code>=HOUR(d\u1eef li\u1ec7u)<\/code><\/pre>\n<p>H\u00e0m tr\u00ean s\u1ebd tr\u1ea3 v\u1ec1 gi\u1edd t\u01b0\u01a1ng \u1ee9ng.<\/p>\n<figure class=\"wp-block-table\">\n<table>\n<tbody>\n<tr>\n<td>Time<\/td>\n<td>Event<\/td>\n<td>=HOUR(A2)<\/td>\n<\/tr>\n<tr>\n<td>3\/3\/2023 15:13<\/td>\n<td>1<\/td>\n<td>15<\/td>\n<\/tr>\n<tr>\n<td>3\/3\/2023 14:16<\/td>\n<td>1<\/td>\n<td>14<\/td>\n<\/tr>\n<tr>\n<td>3\/3\/2023 14:15<\/td>\n<td>1<\/td>\n<td>14<\/td>\n<\/tr>\n<tr>\n<td>2\/27\/2023 12:25<\/td>\n<td>1<\/td>\n<td>12<\/td>\n<\/tr>\n<tr>\n<td>2\/27\/2023 10:57<\/td>\n<td>1<\/td>\n<td>10<\/td>\n<\/tr>\n<tr>\n<td>2\/24\/2023 9:58<\/td>\n<td>1<\/td>\n<td>9<\/td>\n<\/tr>\n<tr>\n<td>2\/21\/2023 8:47<\/td>\n<td>1<\/td>\n<td>8<\/td>\n<\/tr>\n<tr>\n<td>2\/20\/2023 16:07<\/td>\n<td>1<\/td>\n<td>16<\/td>\n<\/tr>\n<tr>\n<td>1\/16\/2023 7:49<\/td>\n<td>1<\/td>\n<td>7<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/figure>\n<p>B\u1edfi ta c\u1ea7n 1 kho\u1ea3ng th\u1eddi gian 3 ti\u1ebfng, n\u00ean d\u1eef li\u1ec7u c\u1ee7a ta s\u1ebd chia cho 3 v\u00e0 l\u1ea5y ph\u1ea7n nguy\u00ean. \u1ede \u0111\u00e2y ta c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng h\u00e0m INT ho\u1eb7c QUOTIENT<\/p>\n<pre class=\"wp-block-code\"><code>=INT(HOUR([TIME])\/3) ho\u1eb7c =INT(c\u1ed9t b\u00ean tr\u00e1i\/3) <\/code><\/pre>\n<figure class=\"wp-block-table\">\n<table>\n<tbody>\n<tr>\n<td>Time<\/td>\n<td>Event<\/td>\n<td>=HOUR(A2)<\/td>\n<td>=INT(HOUR(A2)\/3)<\/td>\n<\/tr>\n<tr>\n<td>3\/3\/2023 15:13<\/td>\n<td>1<\/td>\n<td>15<\/td>\n<td>5<\/td>\n<\/tr>\n<tr>\n<td>3\/3\/2023 14:16<\/td>\n<td>1<\/td>\n<td>14<\/td>\n<td>4<\/td>\n<\/tr>\n<tr>\n<td>3\/3\/2023 14:15<\/td>\n<td>1<\/td>\n<td>14<\/td>\n<td>4<\/td>\n<\/tr>\n<tr>\n<td>2\/27\/2023 12:25<\/td>\n<td>1<\/td>\n<td>12<\/td>\n<td>4<\/td>\n<\/tr>\n<tr>\n<td>2\/27\/2023 10:57<\/td>\n<td>1<\/td>\n<td>10<\/td>\n<td>3<\/td>\n<\/tr>\n<tr>\n<td>2\/24\/2023 9:58<\/td>\n<td>1<\/td>\n<td>9<\/td>\n<td>3<\/td>\n<\/tr>\n<tr>\n<td>2\/21\/2023 8:47<\/td>\n<td>1<\/td>\n<td>8<\/td>\n<td>2<\/td>\n<\/tr>\n<tr>\n<td>2\/20\/2023 16:07<\/td>\n<td>1<\/td>\n<td>16<\/td>\n<td>5<\/td>\n<\/tr>\n<tr>\n<td>1\/16\/2023 7:49<\/td>\n<td>1<\/td>\n<td>7<\/td>\n<td>2<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/figure>\n<p>T\u1edbi b\u01b0\u1edbc n\u00e0y c\u00f3 th\u1ec3 th\u1ea5y ta \u0111\u00e3 \u201cnh\u00f3m\u201d c\u00e1c m\u1ed1c th\u1eddi gian v\u00e0o c\u00e1c nh\u00f3m t\u01b0\u01a1ng \u1ee9ng: t\u1eeb 12h t\u1edbi 15h \u1edf nh\u00f3m 4, t\u1eeb 9h t\u1edbi 12h \u1edf nh\u00f3m 3,\u2026<\/p>\n<p>B\u01b0\u1edbc cu\u1ed1i c\u00f9ng, ta s\u1ebd t\u1ea1o 1 nh\u00e3n d\u00e1n cho t\u1eebng nh\u00f3m. Quy \u0111\u1ecbnh l\u00e0 t\u1eeb 0h t\u1edbi 3h thu\u1ed9c nh\u00f3m 1, 3h t\u1edbi 6h thu\u1ed9c nh\u00f3m 2\u2026 t\u1eeb \u0111\u00f3 ta c\u00f3 c\u00f4ng th\u1ee9c nh\u01b0 sau:<\/p>\n<pre class=\"wp-block-code\"><code>=3*INT(HOUR([TIME])\/3)&amp;\u201d-\u201d&amp;3*INT(HOUR([TIME])\/3)+3 ho\u1eb7c =3*c\u1ed9t b\u00ean tr\u00e1i&amp;\u201d-\u201d&amp;3*c\u1ed9t b\u00ean tr\u00e1i + 3<\/code><\/pre>\n<figure class=\"wp-block-table\">\n<table>\n<tbody>\n<tr>\n<td>Time<\/td>\n<td>Event<\/td>\n<td>=HOUR(A2)<\/td>\n<td>=INT(HOUR(A2)\/3)<\/td>\n<td>=3<em>[@Convert]&amp;\u201d-\u201c&amp;3<\/em>[@Convert]+3<\/td>\n<\/tr>\n<tr>\n<td>3\/3\/2023 15:13<\/td>\n<td>1<\/td>\n<td>15<\/td>\n<td>5<\/td>\n<td>15-18<\/td>\n<\/tr>\n<tr>\n<td>3\/3\/2023 14:16<\/td>\n<td>1<\/td>\n<td>14<\/td>\n<td>4<\/td>\n<td>12-15<\/td>\n<\/tr>\n<tr>\n<td>3\/3\/2023 14:15<\/td>\n<td>1<\/td>\n<td>14<\/td>\n<td>4<\/td>\n<td>12-15<\/td>\n<\/tr>\n<tr>\n<td>2\/27\/2023 12:25<\/td>\n<td>1<\/td>\n<td>12<\/td>\n<td>4<\/td>\n<td>12-15<\/td>\n<\/tr>\n<tr>\n<td>2\/27\/2023 10:57<\/td>\n<td>1<\/td>\n<td>10<\/td>\n<td>3<\/td>\n<td>9-12<\/td>\n<\/tr>\n<tr>\n<td>2\/24\/2023 9:58<\/td>\n<td>1<\/td>\n<td>9<\/td>\n<td>3<\/td>\n<td>9-12<\/td>\n<\/tr>\n<tr>\n<td>2\/21\/2023 8:47<\/td>\n<td>1<\/td>\n<td>8<\/td>\n<td>2<\/td>\n<td>6-9<\/td>\n<\/tr>\n<tr>\n<td>2\/20\/2023 16:07<\/td>\n<td>1<\/td>\n<td>16<\/td>\n<td>5<\/td>\n<td>15-18<\/td>\n<\/tr>\n<tr>\n<td>1\/16\/2023 7:49<\/td>\n<td>1<\/td>\n<td>7<\/td>\n<td>2<\/td>\n<td>6-9<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/figure>\n<p>B\u01b0\u1edbc ti\u1ebfp theo ta load d\u1eef li\u1ec7u v\u00e0o PivotTable, k\u00e9o c\u1ed9t cu\u1ed1i c\u00f9ng v\u00e0o Rows v\u00e0 Event v\u00e0o Values<\/p>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"412\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/image-8-1024x412-2.png\" class=\"wp-image-40713\" title=\"image-8-1024x412-2\"><\/figure>\n<\/div>\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Lay_du_lieu_theo_khoang_thoi_gian_phut_giay\"><\/span>L\u1ea5y d\u1eef li\u1ec7u theo kho\u1ea3ng th\u1eddi gian ph\u00fat, gi\u00e2y<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>T\u01b0\u01a1ng t\u1ef1 v\u1edbi l\u1ea5y kho\u1ea3ng theo gi\u1edd, ta c\u00f3 th\u1ec3 l\u1ea5y chi ti\u1ebft h\u01a1n theo ph\u00fat, gi\u00e2y (th\u01b0\u1eddng s\u1ebd theo ph\u00fat). Trong tr\u01b0\u1eddng h\u1ee3p \u0111\u00f3, ta c\u1ea7n s\u1eeda m\u1ed9t ch\u00fat \u1edf nh\u1eefng c\u1ed9t ph\u1ee5 th\u00eam v\u00e0o<\/p>\n<p>C\u1ed9t tr\u00edch xu\u1ea5t th\u1eddi gian: l\u1ea5y ph\u00fat thay v\u00ec gi\u1edd<\/p>\n<pre class=\"wp-block-code\"><code><s>=HOUR(A2<\/s>) <strong>=MINUTE(A2)<\/strong><\/code><\/pre>\n<p>C\u1ed9t chia kho\u1ea3ng: L\u00e0m t\u01b0\u01a1ng t\u1ef1, chia cho s\u1ed1 ph\u00fat mu\u1ed1n chia kho\u1ea3ng<\/p>\n<pre class=\"wp-block-code\"><code>=INT(MINUTE([TIME])\/15) ho\u1eb7c =INT(c\u1ed9t b\u00ean tr\u00e1i\/15)  --15 phut<\/code><\/pre>\n<p>C\u1ed9t t\u1ea1o nh\u00e3n: L\u1ed3ng th\u00eam h\u00e0m \u0111\u1ec3 hi\u1ec3n th\u1ecb \u0111\u1ecbnh d\u1ea1ng gi\u1edd ph\u00fat gi\u00e2y<\/p>\n<pre class=\"wp-block-code\"><code>=TEXT(TIME(HOUR([@Time]),15*[@[Each_15_mins]],0),\"hh:mm:ss\")&amp;\"-\"&amp;TEXT(TIME(HOUR([@Time]),15*[@[Each_15_mins]]+15,0),\"hh:mm:ss\")<\/code><\/pre>\n<p>K\u1ebft qu\u1ea3:<\/p>\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"366\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/image-9-1024x366-2.png\" class=\"wp-image-40714\" title=\"image-9-1024x366-2\"><\/figure>\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n<p>Xem th\u00eam c\u00e1c b\u00e0i vi\u1ebft c\u00f9ng ch\u1ee7 \u0111\u1ec1:<br \/><a href=\"http:\/\/blog.ezworkapp.com\/lam-nao-de-cong-tru-khoang-thoi-gian-lon-hon-24-gio-60-phut-60-giay-trong-excel.html\" target=\"_blank\" rel=\"noreferrer noopener\"><strong>C\u00e1ch c\u1ed9ng tr\u1eeb kho\u1ea3ng th\u1eddi gian trong Excel<\/strong><\/a><br \/><a href=\"http:\/\/blog.ezworkapp.com\/cach-xac-dinh-ngay-thang-nam-bang-cac-ham-thoi-gian-trong-excel-co-bai-tap.html\" target=\"_blank\" rel=\"noreferrer noopener\"><strong>S\u1eed d\u1ee5ng c\u00e1c h\u00e0m ng\u00e0y th\u00e1ng n\u0103m trong Excel<\/strong><\/a><\/p>\n<\/div>\n<div class=\"uk-panel uk-margin uk-width-xlarge\">\n<p>Click \u0111\u0103ng k\u00fd h\u1ecdc t\u1ea1i:<\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/khoa-hoc\" target=\"_blank\" rel=\"noopener\">T\u1ea5t c\u1ea3 kho\u00e1 h\u1ecdc\u00a0<span>\ud83d\udcd6<\/span><\/a><\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-kich-hoat-khoa-hoc-tai-hoc-excel-online.html\" target=\"_blank\" rel=\"noopener\">H\u01b0\u1edbng d\u1eabn k\u00edch ho\u1ea1t<\/a><\/p>\n<p>HKD H\u1ecdc Excel Online<br \/>S\u1ed1 \u0110K: 17A80048102<\/p>\n<\/div>\n<div class=\"uk-panel uk-text-small uk-margin uk-margin-remove-bottom uk-text-center\">\n<p>\u00a9  document.currentScript.insertAdjacentHTML(&#8216;afterend&#8217;, &#8221; + new Intl.DateTimeFormat(document.documentElement.lang, {year: &#8216;numeric&#8217;}).format() + &#8221;);  H\u1ecdc Excel Online. All rights reserved.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<div class=\"ad-inserterpro\">\n<a href=\"https:\/\/hocexcel.online\/course\/preview\/ex101-excel-tu-co-ban-den-chuyen-gia-danh-cho-nguoi-di-lam\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/ex101_92019-nho-49.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":844,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-843","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-co-ban"],"_links":{"self":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/843","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/comments?post=843"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/843\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/844"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=843"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=843"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=843"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}