{"id":5203,"date":"2025-11-24T11:45:06","date_gmt":"2025-11-24T04:45:06","guid":{"rendered":"https:\/\/autofillform.com\/blog\/huong-dan-cach-dung-ham-offset-trong-excel-cuc-de-hieu\/"},"modified":"2025-11-24T11:45:06","modified_gmt":"2025-11-24T04:45:06","slug":"huong-dan-cach-dung-ham-offset-trong-excel-cuc-de-hieu","status":"publish","type":"post","link":"https:\/\/autofillform.com\/blog\/huong-dan-cach-dung-ham-offset-trong-excel-cuc-de-hieu\/","title":{"rendered":"H\u01b0\u1edbng d\u1eabn c\u00e1ch d\u00f9ng h\u00e0m OFFSET trong Excel c\u1ef1c d\u1ec5 hi\u1ec3u"},"content":{"rendered":"
\n
\u00a0<\/div>\n

Trong Excel, h\u00e0m OFFSET l\u00e0 m\u1ed9t c\u00f4ng c\u1ee5 linh ho\u1ea1t gi\u00fap b\u1ea1n t\u1ea1o ra v\u00f9ng tham chi\u1ebfu \u0111\u1ed9ng d\u1ef1a tr\u00ean v\u1ecb tr\u00ed ban \u0111\u1ea7u c\u1ee7a d\u1eef li\u1ec7u. Nh\u1edd kh\u1ea3 n\u0103ng linh ho\u1ea1t theo h\u00e0ng v\u00e0 c\u1ed9t, h\u00e0m n\u00e0y h\u1ed7 tr\u1ee3 t\u1ed1t cho vi\u1ec7c truy xu\u1ea5t d\u1eef li\u1ec7u, x\u00e2y d\u1ef1ng ph\u1ea1m vi t\u1ef1 \u0111\u1ed9ng v\u00e0 k\u1ebft h\u1ee3p v\u1edbi c\u00e1c h\u00e0m kh\u00e1c \u0111\u1ec3 t\u00ednh to\u00e1n hi\u1ec7u qu\u1ea3 h\u01a1n. B\u00e0i vi\u1ebft sau s\u1ebd h\u01b0\u1edbng d\u1eabn b\u1ea1n s\u1eed d\u1ee5ng OFFSET chi ti\u1ebft v\u00e0 d\u1ec5 hi\u1ec3u.<\/p>\n

\"MinhC\u00e1ch s\u1eed d\u1ee5ng h\u00e0m OFFSET trong Excel \u0111\u01a1n gi\u1ea3n<\/em><\/p>\n

\n

1. H\u00e0m OFFSET trong Excel l\u00e0 g\u00ec?<\/h2>\n<\/div>\n

H\u00e0m OFFSET trong Excel l\u00e0 h\u00e0m gi\u00fap b\u1ea1n t\u1ea1o ra m\u1ed9t v\u00f9ng tham chi\u1ebfu m\u1edbi d\u1ef1a tr\u00ean m\u1ed9t \u00f4 ho\u1eb7c m\u1ed9t d\u00e3y trong b\u1ea3ng t\u00ednh \u0111\u00e3 \u0111\u01b0\u1ee3c x\u00e1c \u0111\u1ecbnh tr\u01b0\u1edbc \u0111\u00f3.<\/p>\n

H\u00e0m OFFSET th\u01b0\u1eddng \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng khi c\u1ea7n:<\/p>\n

    \n
  • L\u1ea5y d\u1eef li\u1ec7u c\u1ee7a m\u1ed9t \u00f4 ho\u1eb7c m\u1ed9t v\u00f9ng n\u1eb1m c\u00e1ch v\u00f9ng g\u1ed1c m\u1ed9t kho\u1ea3ng nh\u1ea5t \u0111\u1ecbnh.<\/li>\n
  • T\u1ea1o c\u00e1c v\u00f9ng d\u1eef li\u1ec7u \u0111\u1ed9ng \u0111\u1ec3 k\u1ebft h\u1ee3p v\u1edbi nh\u1eefng h\u00e0m kh\u00e1c (nh\u01b0 SUM, AVERAGE, COUNTA\u2026), gi\u00fap vi\u1ec7c t\u00ednh to\u00e1n linh ho\u1ea1t v\u00e0 t\u1ef1 \u0111\u1ed9ng h\u01a1n.<\/li>\n<\/ul>\n
    Xem th\u00eam: <\/strong>C\u00e1ch d\u00f9ng h\u00e0m ROUNDDOWN trong Excel \u0111\u1ec3 l\u00e0m tr\u00f2n gi\u00e1 tr\u1ecb<\/div>\n
    \n

    2. C\u00fa ph\u00e1p c\u1ee7a h\u00e0m OFFSET<\/h2>\n<\/div>\n

    C\u00fa ph\u00e1p: =OFFSET(reference, rows, cols, [height], [width])<\/strong><\/p>\n

    Gi\u1ea3i th\u00edch:<\/p>\n

      \n
    • reference (B\u1eaft bu\u1ed9c):<\/strong> \u00d4 ho\u1eb7c v\u00f9ng d\u1eef li\u1ec7u l\u00e0m \u0111i\u1ec3m b\u1eaft \u0111\u1ea7u \u0111\u1ec3 t\u1ea1o tham chi\u1ebfu m\u1edbi.<\/li>\n
    • rows (B\u1eaft bu\u1ed9c): <\/strong>S\u1ed1 h\u00e0ng c\u1ea7n d\u1ecbch chuy\u1ec3n l\u00ean\/xu\u1ed1ng t\u00ednh t\u1eeb reference.<\/li>\n
    • cols (B\u1eaft bu\u1ed9c): <\/strong>S\u1ed1 c\u1ed9t c\u1ea7n d\u1ecbch chuy\u1ec3n sang tr\u00e1i\/ph\u1ea3i t\u00ednh t\u1eeb reference.<\/li>\n
    • height (T\u00f9y ch\u1ecdn):<\/strong> S\u1ed1 h\u00e0ng c\u1ee7a v\u00f9ng k\u1ebft qu\u1ea3 mu\u1ed1n tr\u1ea3 v\u1ec1.<\/li>\n
    • width (T\u00f9y ch\u1ecdn): <\/strong>S\u1ed1 c\u1ed9t c\u1ee7a v\u00f9ng k\u1ebft qu\u1ea3 mu\u1ed1n tr\u1ea3 v\u1ec1.<\/li>\n<\/ul>\n

      \"C\u00faC\u00fa ph\u00e1p h\u00e0m OFFSET trong Excel<\/em><\/p>\n

      \n

      3. V\u00ed d\u1ee5 v\u1ec1 h\u00e0m OFFSET trong Excel<\/h2>\n<\/div>\n

      V\u00ed d\u1ee5: <\/strong>H\u00e3y s\u1eed d\u1ee5ng h\u00e0m OFFSET \u0111\u1ec3 l\u1ea5y ra gi\u00e1 tr\u1ecb \u00f4 \u1edf v\u1ecb tr\u00ed c\u1ed9t 2 d\u00f2ng 1 trong b\u1ea3ng d\u1eef li\u1ec7u.<\/p>\n

      B\u01b0\u1edbc 1: <\/strong>T\u1ea1i \u00f4 b\u1ea1n mu\u1ed1n hi\u1ec3n th\u1ecb k\u1ebft qu\u1ea3, nh\u1eadp c\u00f4ng th\u1ee9c =OFFSET(B3,1,2) \u0111\u1ec3 t\u1ea1o tham chi\u1ebfu m\u1edbi d\u1ef1a tr\u00ean \u00f4 B3.<\/p>\n

      \"OFFSETNh\u1eadp h\u00e0m OFFSET v\u00e0o \u00f4 t\u00ednh<\/em><\/p>\n

      B\u01b0\u1edbc 2: <\/strong>Nh\u1ea5n Enter \u0111\u1ec3 hi\u1ec3n th\u1ecb gi\u00e1 tr\u1ecb t\u1ea1i v\u1ecb tr\u00ed m\u1edbi sau khi d\u1ecbch chuy\u1ec3n.<\/p>\n

      \"OFFSETK\u1ebft qu\u1ea3 gi\u00e1 tr\u1ecb t\u00ecm \u0111\u01b0\u1ee3c<\/em><\/p>\n

      \n

      4. H\u00e0m OFFSET k\u1ebft h\u1ee3p v\u1edbi nh\u1eefng h\u00e0m kh\u00e1c<\/h2>\n<\/div>\n
      \n

      4.1 K\u1ebft h\u1ee3p v\u1edbi h\u00e0m SUM<\/h3>\n<\/div>\n

      V\u00ed d\u1ee5:<\/strong> T\u00ednh t\u1ed5ng gi\u00e1 tr\u1ecb th\u01b0\u1edfng c\u00e1c th\u00e1ng trong b\u1ea3ng d\u1eef li\u1ec7u<\/p>\n

      B\u01b0\u1edbc 1: <\/strong>T\u1ea1i \u00f4 c\u1ea7n t\u00ednh t\u1ed5ng, nh\u1eadp c\u00f4ng th\u1ee9c =SUM(B2:OFFSET(B9,-1,0)) \u0111\u1ec3 t\u1ea1o ph\u1ea1m vi c\u1ed9ng t\u1eeb B2 \u0111\u1ebfn \u00f4 n\u1eb1m ngay ph\u00eda tr\u00ean B9.<\/p>\n

      \"H\u00e0mNh\u1eadp h\u00e0m SUM v\u1edbi OFFSET<\/em><\/p>\n

      B\u01b0\u1edbc 2: <\/strong>Nh\u1ea5n Enter \u0111\u1ec3 Excel th\u1ef1c hi\u1ec7n t\u00ednh to\u00e1n v\u00e0 tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 t\u1ed5ng gi\u00e1 tr\u1ecb th\u01b0\u1edfng trong c\u1ed9t B.<\/p>\n

      \"K\u1ebftK\u1ebft qu\u1ea3 t\u1ed5ng th\u01b0\u1edfng \u0111\u01b0\u1ee3c t\u00ednh<\/em><\/p>\n

      \n

      4.2 K\u1ebft h\u1ee3p v\u1edbi h\u00e0m AVERAGE, MAX, MIN<\/h3>\n<\/div>\n

      V\u00ed d\u1ee5:<\/strong> T\u00ednh gi\u00e1 tr\u1ecb trung b\u00ecnh ti\u1ec1n th\u01b0\u1edfng c\u1ee7a c\u00e1c th\u00e1ng v\u00e0 t\u00ecm xem s\u1ed1 ti\u1ec1n th\u01b0\u1edfng l\u1edbn nh\u1ea5t v\u00e0 nh\u1ecf nh\u1ea5t trong b\u1ea3ng d\u1eef li\u1ec7u<\/p>\n

      B\u01b0\u1edbc 1: <\/strong>\u0110\u1ec3 t\u00ednh gi\u00e1 tr\u1ecb trung b\u00ecnh c\u1ee7a d\u1ea3i d\u1eef li\u1ec7u, nh\u1eadp c\u00f4ng th\u1ee9c =AVERAGE(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1)) v\u00e0o \u00f4 c\u1ea7n hi\u1ec3n th\u1ecb k\u1ebft qu\u1ea3.<\/p>\n

      \"H\u00e0mT\u00ednh gi\u00e1 tr\u1ecb trung b\u00ecnh b\u1eb1ng OFFSET<\/em><\/p>\n

      B\u01b0\u1edbc 2: <\/strong>Nh\u1ea5n Enter \u0111\u1ec3 xem k\u1ebft qu\u1ea3 trung b\u00ecnh.<\/p>\n

      B\u01b0\u1edbc 3: <\/strong>\u0110\u1ec3 x\u00e1c \u0111\u1ecbnh gi\u00e1 tr\u1ecb l\u1edbn nh\u1ea5t v\u00e0 nh\u1ecf nh\u1ea5t trong c\u00f9ng d\u1ea3i d\u1eef li\u1ec7u, b\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng c\u00fa ph\u00e1p:<\/p>\n

        \n
      • Gi\u00e1 tr\u1ecb l\u1edbn nh\u1ea5t: =MAX(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1))<\/li>\n
      • Gi\u00e1 tr\u1ecb nh\u1ecf nh\u1ea5t: =MIN(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1))<\/li>\n<\/ul>\n

        \"H\u00e0mC\u00f4ng th\u1ee9c t\u00ecm gi\u00e1 tr\u1ecb l\u1edbn nh\u1ea5t d\u1ea3i d\u1eef li\u1ec7u<\/em><\/p>\n

        Sau \u0111\u00f3 nh\u1ea5n Enter \u0111\u1ec3 xem gi\u00e1 tr\u1ecb t\u01b0\u01a1ng \u1ee9ng.<\/p>\n

        \n

        4.3 K\u1ebft h\u1ee3p v\u1edbi h\u00e0m VLOOKUP<\/h3>\n<\/div>\n

        V\u00ed d\u1ee5:<\/strong> Tra c\u1ee9u ti\u1ec1n th\u01b0\u1edfng th\u00e1ng 5 b\u1eb1ng VLOOKUP<\/p>\n

        B\u01b0\u1edbc 1:<\/strong> T\u1ea1i \u00f4 b\u1ea1n mu\u1ed1n xem k\u1ebft qu\u1ea3, nh\u1eadp c\u00f4ng th\u1ee9c =VLOOKUP(B1,A5:B11,2,FALSE) \u0111\u1ec3 tra c\u1ee9u gi\u00e1 tr\u1ecb d\u1ef1a tr\u00ean d\u1eef li\u1ec7u trong b\u1ea3ng A5:B11.<\/p>\n

        \"C\u00f4ngD\u00f9ng VLOOKUP tra c\u1ee9u th\u01b0\u1edfng<\/em><\/p>\n

        B\u01b0\u1edbc 2:<\/strong> Nh\u1ea5n Enter \u0111\u1ec3 Excel tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 t\u01b0\u01a1ng \u1ee9ng v\u1edbi n\u1ed9i dung c\u1ea7n t\u00ecm trong \u00f4 B1.<\/p>\n

        \"K\u1ebftK\u1ebft qu\u1ea3 th\u01b0\u1edfng th\u00e1ng c\u1ea7n t\u00ecm<\/em><\/p>\n

        \n

        5. M\u1ed9t s\u1ed1 l\u01b0u \u00fd khi s\u1eed d\u1ee5ng h\u00e0m OFFSET<\/h2>\n<\/div>\n

        D\u01b0\u1edbi \u0111\u00e2y l\u00e0 nh\u1eefng l\u01b0u \u00fd gi\u00fap b\u1ea1n s\u1eed d\u1ee5ng h\u00e0m OFFSET ch\u00ednh x\u00e1c h\u01a1n v\u00e0 h\u1ea1n ch\u1ebf c\u00e1c l\u1ed7i th\u01b0\u1eddng g\u1eb7p trong qu\u00e1 tr\u00ecnh l\u00e0m vi\u1ec7c v\u1edbi Excel:<\/p>\n

          \n
        • L\u1ed7i #REF! xu\u1ea5t hi\u1ec7n khi v\u00f9ng tham chi\u1ebfu v\u01b0\u1ee3t ph\u1ea1m vi trang t\u00ednh: L\u1ed7i n\u00e0y x\u1ea3y ra khi b\u1ea1n nh\u1eadp gi\u00e1 tr\u1ecb rows ho\u1eb7c cols qu\u00e1 l\u1edbn (ho\u1eb7c qu\u00e1 nh\u1ecf theo h\u01b0\u1edbng \u00e2m), khi\u1ebfn v\u00f9ng OFFSET d\u1ecbch chuy\u1ec3n ra ngo\u00e0i ph\u1ea1m vi cho ph\u00e9p c\u1ee7a Excel. Khi \u0111\u00f3 Excel kh\u00f4ng th\u1ec3 x\u00e1c \u0111\u1ecbnh \u00f4 tham chi\u1ebfu h\u1ee3p l\u1ec7 n\u00ean b\u00e1o l\u1ed7i.<\/li>\n
        • Khi b\u1ea1n kh\u00f4ng khai b\u00e1o height v\u00e0 width, Excel s\u1ebd t\u1ef1 hi\u1ec3u v\u00f9ng tr\u1ea3 v\u1ec1 c\u00f3 k\u00edch th\u01b0\u1edbc gi\u1ed1ng v\u1edbi v\u00f9ng tham chi\u1ebfu ban \u0111\u1ea7u.<\/li>\n
        • Tr\u01b0\u1eddng h\u1ee3p height ho\u1eb7c width l\u1edbn h\u01a1n 1, b\u1ea1n c\u1ea7n d\u00f9ng c\u00f4ng th\u1ee9c m\u1ea3ng; n\u1ebfu kh\u00f4ng Excel s\u1ebd b\u00e1o l\u1ed7i #VALUE!. Ngo\u00e0i ra, c\u1ea3 hai \u0111\u1ed1i s\u1ed1 n\u00e0y \u0111\u1ec1u ph\u1ea3i l\u00e0 s\u1ed1 d\u01b0\u01a1ng \u0111\u1ec3 c\u00f4ng th\u1ee9c ho\u1ea1t \u0111\u1ed9ng \u0111\u00fang.<\/li>\n<\/ul>\n

          Vi\u1ec7c n\u1eafm r\u00f5 c\u00e1ch v\u1eadn d\u1ee5ng OFFSET s\u1ebd gi\u00fap b\u1ea1n thao t\u00e1c d\u1eef li\u1ec7u linh ho\u1ea1t v\u00e0 ch\u1ee7 \u0111\u1ed9ng h\u01a1n trong nhi\u1ec1u t\u00ecnh hu\u1ed1ng. Khi \u0111\u00e3 quen v\u1edbi c\u1ea5u tr\u00fac v\u00e0 c\u00e1ch thi\u1ebft l\u1eadp ph\u1ea1m vi tham chi\u1ebfu, b\u1ea1n c\u00f3 th\u1ec3 d\u1ec5 d\u00e0ng \u00e1p d\u1ee5ng h\u00e0m n\u00e0y cho c\u00e1c b\u1ea3ng t\u00ednh ph\u1ee9c t\u1ea1p h\u01a1n.<\/p>\n

          N\u1ebfu mu\u1ed1n c\u1eadp nh\u1eadt th\u00eam c\u00e1c th\u1ee7 thu\u1eadt h\u1eefu \u00edch cho c\u00f4ng vi\u1ec7c h\u1eb1ng ng\u00e0y, h\u00e3y theo d\u00f5i chuy\u00ean m\u1ee5c Kinh nghi\u1ec7m t\u1ea1i Si\u00eau Th\u1ecb \u0110i\u1ec7n M\u00e1y – N\u1ed9i Th\u1ea5t Ch\u1ee3 L\u1edbn.<\/p>\n

          N\u1ebfu b\u1ea1n c\u1ea7n m\u1ed9t chi\u1ebfc m\u00e1y t\u00ednh b\u1ea3ng ho\u1eb7c \u0111i\u1ec7n tho\u1ea1i \u0111\u1ec3 thao t\u00e1c Excel ti\u1ec7n l\u1ee3i h\u01a1n, h\u00e3y \u0111\u1ebfn ngay Si\u00eau Th\u1ecb \u0110i\u1ec7n M\u00e1y – N\u1ed9i Th\u1ea5t Ch\u1ee3 L\u1edbn \u0111\u1ec3 ch\u1ecdn thi\u1ebft b\u1ecb ph\u00f9 h\u1ee3p v\u1edbi nhu c\u1ea7u l\u00e0m vi\u1ec7c v\u00e0 h\u1ecdc t\u1eadp c\u1ee7a b\u1ea1n.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

          \u00a0 Trong Excel, h\u00e0m OFFSET l\u00e0 m\u1ed9t c\u00f4ng c\u1ee5 linh ho\u1ea1t gi\u00fap b\u1ea1n t\u1ea1o ra v\u00f9ng tham chi\u1ebfu \u0111\u1ed9ng d\u1ef1a tr\u00ean v\u1ecb tr\u00ed ban \u0111\u1ea7u c\u1ee7a d\u1eef li\u1ec7u. Nh\u1edd kh\u1ea3 n\u0103ng linh ho\u1ea1t theo h\u00e0ng v\u00e0 c\u1ed9t, h\u00e0m n\u00e0y h\u1ed7 tr\u1ee3 t\u1ed1t cho vi\u1ec7c truy xu\u1ea5t d\u1eef li\u1ec7u, x\u00e2y d\u1ef1ng ph\u1ea1m vi t\u1ef1 \u0111\u1ed9ng v\u00e0 […]<\/p>\n","protected":false},"author":1,"featured_media":5204,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8],"tags":[],"class_list":["post-5203","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-thu-thuat"],"jetpack_featured_media_url":"https:\/\/autofillform.com\/blog\/wp-content\/uploads\/2025\/11\/19437.jpg","_links":{"self":[{"href":"https:\/\/autofillform.com\/blog\/wp-json\/wp\/v2\/posts\/5203","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/autofillform.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/autofillform.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/autofillform.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/autofillform.com\/blog\/wp-json\/wp\/v2\/comments?post=5203"}],"version-history":[{"count":0,"href":"https:\/\/autofillform.com\/blog\/wp-json\/wp\/v2\/posts\/5203\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/autofillform.com\/blog\/wp-json\/wp\/v2\/media\/5204"}],"wp:attachment":[{"href":"https:\/\/autofillform.com\/blog\/wp-json\/wp\/v2\/media?parent=5203"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/autofillform.com\/blog\/wp-json\/wp\/v2\/categories?post=5203"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/autofillform.com\/blog\/wp-json\/wp\/v2\/tags?post=5203"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}