{"id":7694,"date":"2021-08-13T19:48:32","date_gmt":"2021-08-13T12:48:32","guid":{"rendered":"https:\/\/tct.vn\/blog\/?p=7694"},"modified":"2021-08-18T11:08:28","modified_gmt":"2021-08-18T04:08:28","slug":"7694-2","status":"publish","type":"post","link":"https:\/\/tct.vn\/blog\/7694-2\/","title":{"rendered":"H\u01b0\u1edbng d\u1eabn c\u00e1ch vi\u1ebft c\u00f4ng th\u1ee9c l\u1eadp b\u00e1o c\u00e1o chi ti\u1ebft t\u1ef1 \u0111\u1ed9ng trong Excel"},"content":{"rendered":"

C\u00e1ch th\u1ef1c hi\u1ec7n<\/h2>\n

<\/span>B\u01b0\u1edbc 1: T\u1ea1o danh s\u00e1ch ch\u1ecdn t\u00ean m\u1eb7t h\u00e0ng<\/h3>\n

\u1ede b\u1ea3ng k\u00ea b\u00e1n h\u00e0ng c\u00f3 3 m\u1eb7t h\u00e0ng l\u00e0 H\u00e0ng A, H\u00e0ng B, H\u00e0ng C. Do\u00a0\u0111\u00f3 ch\u00fang ta c\u00f3 th\u1ec3 t\u1ea1o danh s\u00e1ch ch\u1ecdn t\u00ean m\u1eb7t h\u00e0ng t\u1ea1i\u00a0\u00f4 H2 nh\u01b0 sau:<\/p>\n

Ch\u1ecdn tab Data \/ Data Validation<\/p>\n

\"\"<\/p>\n

\n
\n

D\u00f9 \u0111\u00e3 c\u00f3 ph\u1ea7n m\u1ec1m, nh\u01b0ng k\u1ef9 n\u0103ng Excel v\u1eabn c\u1ef1c k\u1ef3 quan tr\u1ecdng v\u1edbi k\u1ebf to\u00e1n, b\u1ea1n \u0111\u00e3 v\u1eefng Excel ch\u01b0a? H\u00e3y \u0111\u1ec3 t\u00f4i gi\u00fap b\u1ea1n, \u0111\u0103ng k\u00fd kho\u00e1 h\u1ecdc Excel:<\/p>\n<\/div>\n<\/div>\n

\n
Trong b\u1ea3ng Data Validation:<\/span><\/div>\n<\/div>\n
    \n
  • M\u1ee5c Allow ch\u00fang ta ch\u1ecdn List<\/li>\n
  • M\u1ee5c Data nh\u1eadp n\u1ed9i dung nh\u01b0 sau: H\u00e0ng A, H\u00e0ng B, H\u00e0ng C<\/li>\n<\/ul>\n
    \"\"

    B\u1ea5m ok<\/p><\/div>\n

     <\/p>\n

    Khi\u00a0\u0111\u00f3 ch\u00fang ta t\u1ea1i\u00a0\u00f4 H2 ch\u00fang ta c\u00f3 th\u1ec3 ch\u1ecdn t\u00ean h\u00e0ng theo danh s\u00e1ch g\u1ed3m 3 m\u1eb7t h\u00e0ng n\u00e0y.<\/p>\n

    <\/span>B\u01b0\u1edbc 2: C\u00f4ng th\u1ee9c\u00a0l\u1ea5y d\u1eef li\u1ec7u v\u00e0o b\u00e1o c\u00e1o<\/h3>\n

    C\u0103n c\u1ee9 v\u00e0o v\u1ecb tr\u00ed t\u00ean h\u00e0ng x\u00e1c\u00a0\u0111\u1ecbnh\u00a0\u0111\u01b0\u1ee3c\u00a0\u1edf tr\u00ean, l\u1ea5y c\u00e1c n\u1ed9i dung g\u1ed3m: Ng\u00e0y, T\u00ean nh\u00e2n vi\u00ean, s\u1ed1\u00a0l\u01b0\u1ee3ng, th\u00e0nh ti\u1ec1n t\u01b0\u01a1ng\u00a0\u1ee9ng theo m\u1eb7t h\u00e0ng\u00a0\u0111\u00f3 => S\u1eed d\u1ee5ng h\u00e0m\u00a0OFFSET<\/p>\n

    B\u1edfi v\u00ec\u00a0k\u1ebft qu\u1ea3 tr\u1ea3 v\u1ec1 l\u00e0 1 m\u1ea3ng d\u1eef li\u1ec7u n\u00ean ch\u00fang ta c\u1ea7n s\u1eed d\u1ee5ng t\u1edbi c\u00f4ng th\u1ee9c m\u1ea3ng.<\/p>\n

    Khi vi\u1ebft c\u00f4ng th\u1ee9c m\u1ea3ng, ch\u00fang ta ch\u1ecdn to\u00e0n b\u1ed9 v\u00f9ng s\u1ebd th\u1ec3 hi\u1ec7n k\u1ebft qu\u1ea3 (G5:J12), sau\u00a0\u0111\u00f3 nh\u1eadp c\u00f4ng th\u1ee9c v\u00e0 k\u1ebft th\u00fac\u00a0c\u00f4ng th\u1ee9c n\u00e0y ch\u00fang ta s\u1ebd d\u00f9ng t\u1ed5 h\u1ee3p ph\u00edm Ctrl + Shift + Enter<\/p>\n

    C\u1ea5u tr\u00fac h\u00e0m\u00a0OFFSET g\u1ed3m:<\/p>\n

    =OFFSET(reference, rows, cols, [height], [width])<\/p>\n

      \n
    • references: T\u00ean h\u00e0ng\u00a0\u0111\u01b0\u1ee3c ch\u1ecdn\u00a0\u0111\u1ec3 l\u1eadp b\u00e1o c\u00e1o t\u1ea1i\u00a0\u00f4 H2 tr\u00f9ng v\u1edbi t\u00ean h\u00e0ng trong c\u1ed9t A => C\u00f3 th\u1ec3 s\u1eed d\u1ee5ng h\u00e0m Index+Match\u00a0\u0111\u1ec3 x\u00e1c\u00a0\u0111\u1ecbnh v\u1ecb tr\u00ed n\u00e0o\u00a0\u1edf c\u1ed9t A c\u00f3 k\u1ebft qu\u1ea3\u00a0\u0111\u00fang<\/li>\n
    • row: s\u1ed1\u00a0d\u00f2ng b\u1eaft \u0111\u1ea7u t\u00ednh t\u1eeb k\u1ebft qu\u1ea3,\u00a0\u1edf\u00a0\u0111\u00e2y t\u00ednh t\u1ea1i d\u00f2ng\u00a0\u0111\u1ea7u ti\u00ean xu\u1ea5t hi\u1ec7n t\u00ean h\u00e0ng n\u00ean row = 0<\/li>\n
    • cols:\u00a0S\u1ed1 c\u1ed9t b\u1eaft \u0111\u1ea7u\u00a0t\u00ednh t\u1eeb k\u1ebft qu\u1ea3,\u00a0\u1edf\u00a0\u0111\u00e2y l\u1ea5y k\u1ebft qu\u1ea3 b\u1eaft\u00a0\u0111\u1ea7u t\u1eeb c\u1ed9t B t\u1edbi c\u00e1c c\u1ed9t kh\u00e1c (c\u1ed9t B c\u00e1ch c\u1ed9t A l\u00e0 1 c\u1ed9t) n\u00ean cols = 1<\/li>\n
    • \u0110\u1ed9 l\u1edbn c\u1ee7a v\u00f9ng c\u1ea7n l\u1ea5y b\u1edfi h\u00e0m\u00a0OFFSET s\u1ebd l\u00e0:<\/li>\n<\/ul>\n

      +)\u00a0Height (s\u1ed1\u00a0d\u00f2ng): B\u1eb1ng s\u1ed1 d\u00f2ng c\u00f3\u00a0T\u00ean h\u00e0ng t\u1ea1i H2 tr\u00f9ng v\u1edbi c\u1ed9t T\u00ean h\u00e0ng => S\u1eed d\u1ee5ng h\u00e0m COUNTIF\u00a0\u0111\u1ec3\u00a0\u0111\u1ebfm s\u1ed1 l\u1ea7n xu\u1ea5t hi\u1ec7n t\u00ean h\u00e0ng n\u00e0y<\/p>\n

      +)\u00a0Weight (s\u1ed1 c\u1ed9t):\u00a0\u1edf\u00a0\u0111\u00e2y b\u00e1o c\u00e1o chi ti\u1ebft c\u1ea7n 4 c\u1ed9t, do\u00a0\u0111\u00f3 l\u1ea5y 4 c\u1ed9t t\u01b0\u01a1ng\u00a0\u1ee9ng theo t\u00ean h\u00e0ng n\u00e0y.<\/p>\n

      Trong\u00a0\u0111\u00f3:<\/p>\n

        \n
      • H\u00e0m Index + Match\u00a0\u0111\u01b0\u1ee3c vi\u1ebft nh\u01b0 sau:<\/li>\n<\/ul>\n

        INDEX($A$3:$A$12,MATCH($H$2,$A$3:$A$12,0))<\/p>\n

          \n
        • H\u00e0m COUNTIF\u00a0\u0111\u01b0\u1ee3c vi\u1ebft nh\u01b0 sau:<\/li>\n<\/ul>\n

          COUNTIF($A$3:$A$12,$H$2)<\/p>\n

          C\u00f4ng th\u1ee9c ho\u00e0n ch\u1ec9nh l\u00e0:<\/p>\n

          =OFFSET(INDEX($A$3:$A$12,MATCH($H$2,$A$3:$A$12,0)),0,1,COUNTIF($A$3:$A$12,$H$2),4)<\/p>\n

          Ch\u00fang ta c\u00f3 k\u1ebft qu\u1ea3 nh\u01b0 sau:<\/p>\n

          \"\"<\/p>\n

          <\/span>B\u01b0\u1edbc 3:\u00a0C\u00e1ch b\u1eaby l\u1ed7i<\/h3>\n

          Khi s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c\u00a0OFFSET cho v\u00f9ng b\u1ea3ng G5:J12, t\u00ean h\u00e0ng l\u00e0 H\u00e0ng A, ch\u00fang ta thu\u00a0\u0111\u01b0\u1ee3c 4 d\u00f2ng k\u1ebft qu\u1ea3 t\u01b0\u01a1ng \u1ee9ng theo b\u1ea3ng k\u00ea. Nh\u01b0 v\u1eady l\u00e0 k\u1ebft qu\u1ea3 \u0111\u00e3 \u0111\u00fang.<\/p>\n

          Nh\u01b0ng v\u00f9ng b\u1ea3ng G5:J12 c\u00f3 t\u1edbi 8 d\u00f2ng. V\u1eady nh\u1eefng d\u00f2ng th\u1eeba s\u1ebd xu\u1ea5t hi\u1ec7n l\u1ed7i #N\/A<\/p>\n

          \u0110\u1ec3 b\u1eaby l\u1ed7i n\u00e0y, kh\u00f4ng\u00a0\u0111\u1ec3 l\u1ed7i hi\u1ec3n th\u1ecb th\u00ec ch\u00fang ta l\u00e0m nh\u01b0 sau:<\/p>\n

          S\u1eed d\u1ee5ng ch\u1ee9c n\u0103ng Conditional Formatting:<\/p>\n

            \n
          • Ch\u1ecdn to\u00e0n b\u1ed9 b\u1ea3ng t\u00ednh t\u1eeb\u00a0G5:J12<\/strong><\/li>\n
          • Trong tab\u00a0Home<\/strong>\u00a0> Ch\u1ecdn\u00a0Conditional formatting<\/strong>\u00a0> Ch\u1ecdn\u00a0New Rule<\/strong><\/li>\n
          • M\u1ee5c\u00a0Select a Rule Type<\/strong>\u00a0ch\u1ecdn\u00a0Format only cells that conain<\/strong><\/li>\n
          • M\u1ee5c\u00a0Format only cells\u00a0with<\/strong>\u00a0ch\u1ecdn\u00a0Errors<\/strong><\/li>\n<\/ul>\n

            \"\"<\/p>\n

            Ch\u00fang ta s\u1ebd thi\u1ebft l\u1eadp \u0111\u1ecbnh d\u1ea1ng cho c\u00e1c \u00f4 c\u00f3 ch\u1ee9a l\u1ed7i trong v\u00f9ng b\u1ea3ng n\u00e0y trong m\u1ee5c Format > Ch\u1ecdn m\u00e0u ch\u1eef l\u00e0 m\u00e0u tr\u1eafng<\/p>\n

            \"\"<\/p>\n

            B\u1ea5m ok v\u00e0 ch\u00fang ta s\u1ebd thu\u00a0\u0111\u01b0\u1ee3c k\u1ebft qu\u1ea3 nh\u01b0 sau:<\/p>\n

            \"\"<\/p>\n

            <\/span>Nh\u1eefng\u00a0\u0111i\u1ec1u c\u1ea7n l\u01b0u\u00a0\u00fd<\/h2>\n
              \n
            • Do h\u00e0m\u00a0OFFSET l\u1ea5y ra k\u1ebft qu\u1ea3 l\u00e0 1 m\u1ea3ng li\u1ec1n nhau, do\u00a0\u0111\u00f3 d\u1eef li\u1ec7u trong b\u1ea3ng k\u00ea (ph\u1ea7n nguy\u00ean li\u1ec7u\u00a0\u0111\u1ec3 l\u1eadp b\u00e1o c\u00e1o) s\u1ebd ph\u1ea3i\u00a0\u0111\u01b0\u1ee3c s\u1eafp x\u1ebfp theo th\u1ee9 t\u1ef1 theo t\u00ean h\u00e0ng<\/li>\n
            • C\u00e1c c\u1ed9t k\u1ebft qu\u1ea3 trong b\u00e1o c\u00e1o chi ti\u1ebft s\u1ebd l\u1ea5y t\u01b0\u01a1ng\u00a0\u1ee9ng theo v\u1ecb tr\u00ed trong b\u1ea3ng k\u00ea t\u01b0\u01a1ng\u00a0\u1ee9ng theo v\u1ecb tr\u00ed c\u1ed9t t\u00ean h\u00e0ng. Do\u00a0\u0111\u00f3 t\u00f9y theo v\u1ecb tr\u00ed c\u00e1c v\u00f9ng d\u1eef li\u1ec7u c\u1ea7n l\u1ea5y trong b\u00e1o c\u00e1o chi ti\u1ebft m\u00e0 ch\u00fang ta c\u00f3 th\u1ec3 thi\u1ebft l\u1eadp c\u00e1c tham s\u1ed1:\u00a0Row, Cols, Height,\u00a0Weight trong h\u00e0m\u00a0offset cho ph\u00f9 h\u1ee3p.\u00a0\u0110\u1ec3 thu\u1eadn l\u1ee3i h\u01a1n khi l\u00e0m vi\u1ec7c n\u00e0y th\u00ec ch\u00fang ta\u00a0\u0111\u1eb7t c\u00e1c c\u1ed9t k\u1ebft qu\u1ea3 li\u1ec1n k\u1ec1 nhau, tr\u00e1nh vi\u1ec7c c\u1ed9t\u00a0\u0111i\u1ec1u ki\u1ec7n l\u1eadp b\u00e1o c\u00e1o (t\u00ean h\u00e0ng) n\u1eb1m xen gi\u1eefa c\u00e1c c\u1ed9t k\u1ebft qu\u1ea3.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"

              C\u00e1ch th\u1ef1c hi\u1ec7n B\u01b0\u1edbc 1: T\u1ea1o danh s\u00e1ch ch\u1ecdn t\u00ean m\u1eb7t h\u00e0ng \u1ede b\u1ea3ng k\u00ea b\u00e1n h\u00e0ng c\u00f3 3 m\u1eb7t h\u00e0ng l\u00e0 H\u00e0ng A, H\u00e0ng B, H\u00e0ng C. Do\u00a0\u0111\u00f3 ch\u00fang ta c\u00f3 th\u1ec3 t\u1ea1o danh s\u00e1ch ch\u1ecdn t\u00ean m\u1eb7t h\u00e0ng t\u1ea1i\u00a0\u00f4 H2 nh\u01b0 sau: Ch\u1ecdn tab Data \/ Data Validation D\u00f9 \u0111\u00e3 c\u00f3 ph\u1ea7n m\u1ec1m, […]<\/p>\n","protected":false},"author":3,"featured_media":7695,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[508],"tags":[],"class_list":["post-7694","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-office"],"_links":{"self":[{"href":"https:\/\/tct.vn\/blog\/wp-json\/wp\/v2\/posts\/7694","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/tct.vn\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/tct.vn\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/tct.vn\/blog\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/tct.vn\/blog\/wp-json\/wp\/v2\/comments?post=7694"}],"version-history":[{"count":0,"href":"https:\/\/tct.vn\/blog\/wp-json\/wp\/v2\/posts\/7694\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/tct.vn\/blog\/wp-json\/wp\/v2\/media\/7695"}],"wp:attachment":[{"href":"https:\/\/tct.vn\/blog\/wp-json\/wp\/v2\/media?parent=7694"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tct.vn\/blog\/wp-json\/wp\/v2\/categories?post=7694"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tct.vn\/blog\/wp-json\/wp\/v2\/tags?post=7694"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}