{"id":1258,"date":"2024-12-02T04:33:26","date_gmt":"2024-12-02T04:33:26","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/ham-offset-va-ung-dung-cua-ham-offset-trong-excel\/"},"modified":"2024-12-02T04:33:26","modified_gmt":"2024-12-02T04:33:26","slug":"ham-offset-va-ung-dung-cua-ham-offset-trong-excel","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/ham-offset-va-ung-dung-cua-ham-offset-trong-excel\/","title":{"rendered":"H\u00e0m Offset v\u00e0 \u1ee9ng d\u1ee5ng c\u1ee7a h\u00e0m Offset trong Excel"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p><a href=\"http:\/\/blog.ezworkapp.com\/ham-offset-va-cac-ket-hop-ham-cua-no-trong-excel-cong-thuc-vi-du.html\"><strong>H\u00e0m OFFSET trong Excel<\/strong><\/a> \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng trong tr\u01b0\u1eddng h\u1ee3p mu\u1ed1n xem ho\u1eb7c t\u00ednh to\u00e1n, ph\u00e2n t\u00edch d\u1eef li\u1ec7u c\u1ee7a m\u1ed9t \u00f4 hay m\u1ed9t d\u00e3y trong b\u1ea3ng t\u00ednh d\u1ef1a v\u00e0o m\u1ed9t v\u00f9ng tham chi\u1ebfu c\u00f3 s\u1eb5n tr\u01b0\u1edbc \u0111\u00f3.<\/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-674d389583768\" 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-3\"><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/blog.hocexcel.online\/ham-offset-va-ung-dung-cua-ham-offset-trong-excel.html#Cu_phap\" title=\"C\u00fa ph\u00e1p\">C\u00fa ph\u00e1p<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/blog.hocexcel.online\/ham-offset-va-ung-dung-cua-ham-offset-trong-excel.html#Vi_du\" title=\"V\u00ed d\u1ee5:\">V\u00ed d\u1ee5:<\/a><\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h3 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"Cu_phap\"><\/span><b>C\u00fa ph\u00e1p<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p style=\"text-align: justify\"><strong>=OFFSET(reference, rows, cols, height, width) <\/strong><\/p>\n<p style=\"text-align: justify\"><strong>Trong \u0111\u00f3:<\/strong><\/p>\n<ul style=\"text-align: justify\">\n<li><i><b>reference<\/b><\/i>: l\u00e0 v\u00f9ng tham chi\u1ebfu l\u00e0m c\u01a1 s\u1edf cho h\u00e0m (l\u00e0m \u0111i\u1ec3m xu\u1ea5t ph\u00e1t) \u0111\u1ec3 t\u1ea1o v\u00f9ng tham chi\u1ebfu m\u1edbi.<\/li>\n<li><i><b>rows<\/b><\/i>: l\u00e0 s\u1ed1 d\u00f2ng b\u00ean tr\u00ean ho\u1eb7c b\u00ean d\u01b0\u1edbi reference, t\u00ednh t\u1eeb \u00f4 \u0111\u1ea7u ti\u00ean (\u00f4 \u1edf g\u00f3c tr\u00ean b\u00ean tr\u00e1i) c\u1ee7a reference. V\u00ed d\u1ee5 n\u1ebfu rows l\u00e0 3, s\u1ebd c\u00f3 3 d\u00f2ng tr\u1ea3 v\u1ec1 v\u00e0 n\u1eb1m b\u00ean d\u01b0\u1edbi reference. Khi rows l\u00e0 s\u1ed1 d\u01b0\u01a1ng th\u00ec c\u00e1c d\u00f2ng tr\u1ea3 v\u1ec1 n\u1eb1m b\u00ean d\u01b0\u1edbi reference, khi rows l\u00e0 s\u1ed1 \u00e2m th\u00ec c\u00e1c d\u00f2ng tr\u1ea3 v\u1ec1 n\u1eb1m b\u00ean tr\u00ean reference.<\/li>\n<li><i><b>cols<\/b><\/i>: l\u00e0 s\u1ed1 c\u1ed9t b\u00ean tr\u00e1i ho\u1eb7c b\u00ean ph\u1ea3i reference, t\u00ednh t\u1eeb \u00f4 \u0111\u1ea7u ti\u00ean (\u00f4 \u1edf g\u00f3c tr\u00ean b\u00ean tr\u00e1i) c\u1ee7a reference. V\u00ed d\u1ee5 n\u1ebfu cols l\u00e0 4 s\u1ebd c\u00f3 4 c\u1ed9t tr\u1ea3 v\u1ec1 v\u00e0 n\u1eb1m b\u00ean ph\u1ea3i c\u1ee7a reference. Khi cols l\u00e0 s\u1ed1 d\u01b0\u01a1ng th\u00ec c\u00e1c c\u1ed9t tr\u1ea3 v\u1ec1 n\u1eb1m b\u00ean ph\u1ea3i reference, khi cols l\u00e0 s\u1ed1 \u00e2m th\u00ec c\u00e1c c\u1ed9t tr\u1ea3 v\u1ec1 n\u1eb1m b\u00ean tr\u00e1i reference.<\/li>\n<li><i><b>height<\/b><\/i>: l\u00e0 s\u1ed1 d\u00f2ng c\u1ee7a v\u00f9ng tham chi\u1ebfu c\u1ea7n tr\u1ea3 v\u1ec1. Height ph\u1ea3i l\u00e0 s\u1ed1 d\u01b0\u01a1ng.<\/li>\n<li><i><b>width<\/b><\/i>: l\u00e0 s\u1ed1 c\u1ed9t c\u1ee7a v\u00f9ng tham chi\u1ebfu c\u1ea7n tr\u1ea3 v\u1ec1. Width ph\u1ea3i l\u00e0 s\u1ed1 d\u01b0\u01a1ng.<\/li>\n<\/ul>\n<p style=\"text-align: justify\"><strong>Ch\u00fa \u00fd: <\/strong><\/p>\n<ul style=\"text-align: justify\">\n<li>N\u1ebfu rows v\u00e0 cols l\u00e0m cho v\u00f9ng tham chi\u1ebfu v\u01b0\u1ee3t ra kh\u1ecfi ph\u1ea1m vi c\u1ee7a b\u1ea3ng t\u00ednh th\u00ec h\u00e0m OFFSET() s\u1ebd b\u00e1o l\u1ed7i #REF!.<\/li>\n<li>H\u00e0m OFFSET() kh\u00f4ng di chuy\u1ec3n hay l\u00e0m thay \u0111\u1ed5i b\u1ea5t k\u1ef3 ph\u1ea7n n\u00e0o \u0111\u01b0\u1ee3c ch\u1ecdn v\u00ec h\u00e0m ch\u1ec9 tr\u1ea3 v\u1ec1 m\u1ed9t tham chi\u1ebfu. C\u00e1c b\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng h\u00e0m OFFSET() k\u1ebft h\u1ee3p v\u1edbi c\u00e1c h\u00e0m c\u1ea7n \u0111\u1ebfn m\u1ed9t \u0111\u1ed1i s\u1ed1 tham chi\u1ebfu.<\/li>\n<\/ul>\n<h3 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"Vi_du\"><\/span><strong>V\u00ed d\u1ee5:<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p style=\"text-align: justify\">\u2013 T\u1ea1i \u00f4 B7, tr\u1ea3 v\u1ec1\u00a0gi\u00e1 tr\u1ecb c\u1ee7a \u00f4 D5 th\u00f4ng qua \u00f4 tham chi\u1ebfu c\u01a1 s\u1edf B4, d\u00f9ng c\u00f4ng th\u1ee9c:\u00a0<strong>=OFFSET(B4;1;2;1;1)<\/strong><\/p>\n<p style=\"text-align: justify\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-3304 size-full\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/1-28.png\" width=\"600\" height=\"417\" title=\"1-28\"><\/p>\n<p style=\"text-align: justify\">=&gt;K\u1ebft qu\u1ea3:<\/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-89.gif\" title=\"ex101_92019-nho-89\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p style=\"text-align: justify\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-3306 size-full\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/1-29.png\" width=\"600\" height=\"420\" title=\"1-29\"><\/p>\n<p style=\"text-align: justify\">\u2013\u00a0K\u1ebft h\u1ee3p h\u00e0m OFFSET() v\u1edbi h\u00e0m SUM() \u0111\u1ec3 t\u00ednh t\u1ed5ng v\u00f9ng b\u00f4i v\u00e0ng C3-E5, v\u1edbi v\u00f9ng tham chi\u1ebfu l\u00e0 b\u1ea3ng C4-E7 ta d\u00f9ng c\u00f4ng th\u1ee9c:\u00a0<strong>=SUM(OFFSET(C4:E7;-1;0;3;3))<\/strong><\/p>\n<p style=\"text-align: justify\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-3308 size-full\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/1-30.png\" width=\"600\" height=\"384\" title=\"1-30\"><\/p>\n<p style=\"text-align: justify\">=&gt; K\u1ebft qu\u1ea3 thu \u0111\u01b0\u1ee3c:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-3309 size-full\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/1-31.png\" width=\"600\" height=\"391\" title=\"1-31\"><\/p>\n<blockquote>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/khoa-hoc-excel-co-ban\">H\u1ecdc Word Excel c\u01a1 b\u1ea3n<\/a><\/p>\n<\/blockquote>\n<p>Ngo\u00e0i ra c\u00e1c b\u1ea1n c\u00f3 th\u1ec3 tham kh\u1ea3o video sau \u0111\u00e2y \u0111\u1ec3 c\u00f3 th\u1ec3 th\u1ea5y r\u00f5 h\u01a1n c\u00e1ch s\u1eed d\u1ee5ng h\u00e0m OFFSET trong Excel:<\/p>\n<p style=\"text-align: center\"><iframe loading=\"lazy\" src=\"https:\/\/www.youtube.com\/embed\/-_hAQ6IKxSk\" width=\"560\" height=\"314\" allowfullscreen=\"allowfullscreen\"><\/iframe><\/p>\n<p>V\u00e0 1 v\u00ed d\u1ee5 \u1ee9ng d\u1ee5ng th\u1ef1c t\u1ebf c\u1ee7a h\u00e0m offset trong 1 group facebook:<\/p>\n<p><a href=\"https:\/\/www.facebook.com\/photo.php?fbid=10154749371733241&amp;l=32c34cb1a6\">https:\/\/www.facebook.com\/photo.php?fbid=10154749371733241&amp;l=32c34cb1a6<\/a><\/p>\n<p><a href=\"https:\/\/www.facebook.com\/photo.php?fbid=10154749371733241&amp;l=32c34cb1a6\">https:\/\/www.facebook.com\/photo.php?fbid=10154749371733241&amp;l=32c34cb1a6<\/a><\/p>\n<p>\u0110\u1ec3 c\u00f3 th\u1ec3 \u1ee9ng d\u1ee5ng t\u1ed1t Excel v\u00e0o trong c\u00f4ng vi\u1ec7c, ch\u00fang ta kh\u00f4ng ch\u1ec9 n\u1eafm v\u1eefng \u0111\u01b0\u1ee3c c\u00e1c h\u00e0m m\u00e0 c\u00f2n ph\u1ea3i s\u1eed d\u1ee5ng t\u1ed1t c\u1ea3 c\u00e1c c\u00f4ng c\u1ee5 c\u1ee7a Excel. Nh\u1eefng h\u00e0m n\u00e2ng cao gi\u00fap \u00e1p d\u1ee5ng t\u1ed1t v\u00e0o c\u00f4ng vi\u1ec7c nh\u01b0 SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH\u00a0 \u2026 Nh\u1eefng c\u00f4ng c\u1ee5 th\u01b0\u1eddng s\u1eed d\u1ee5ng l\u00e0 Data validation, Conditional formatting, Pivot table \u2026<\/p>\n<p>To\u00e0n b\u1ed9 nh\u1eefng ki\u1ebfn th\u1ee9c n\u00e0y c\u00e1c b\u1ea1n \u0111\u1ec1u c\u00f3 th\u1ec3 h\u1ecdc \u0111\u01b0\u1ee3c trong kh\u00f3a h\u1ecdc\u00a0<strong><a href=\"http:\/\/bit.ly\/2mWmtzP\">EX101 \u2013 Excel t\u1eeb c\u01a1 b\u1ea3n t\u1edbi chuy\u00ean gia<\/a><\/strong> c\u1ee7a <a href=\"http:\/\/blog.ezworkapp.com\/\"><strong>H\u1ecdc Excel Online<\/strong><\/a>. Hi\u1ec7n nay h\u1ec7 th\u1ed1ng \u0111ang c\u00f3 \u01b0u \u0111\u00e3i r\u1ea5t l\u1edbn cho b\u1ea1n khi \u0111\u0103ng k\u00fd tham gia kh\u00f3a h\u1ecdc.<\/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-89.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":1259,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-1258","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-nang-cao"],"_links":{"self":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/1258","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=1258"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/1258\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/1259"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=1258"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=1258"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=1258"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}