{"id":7090,"date":"2024-12-21T16:51:35","date_gmt":"2024-12-21T16:51:35","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/21\/tao-muc-luc-bang-tinh-trong-file-excel\/"},"modified":"2024-12-21T16:51:35","modified_gmt":"2024-12-21T16:51:35","slug":"tao-muc-luc-bang-tinh-trong-file-excel","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/21\/tao-muc-luc-bang-tinh-trong-file-excel\/","title":{"rendered":"T\u1ea1o m\u1ee5c l\u1ee5c b\u1ea3ng t\u00ednh trong file Excel"},"content":{"rendered":"
\n

Ch\u00e0o c\u00e1c b\u1ea1n, h\u00f4m nay ch\u00fang ta s\u1ebd c\u00f3 m\u1ed9t nhi\u1ec7m v\u1ee5 l\u00e0 \u0111i t\u1ea1o m\u1ee5c l\u1ee5c<\/strong> b\u1ea3ng t\u00ednh trong file Excel c\u1ee7a m\u00ecnh. Khi b\u1ea1n c\u00f3 1 file Excel v\u1edbi r\u1ea5t nhi\u1ec1u sheets trong \u0111\u00f3 v\u00e0 b\u1ea1n mu\u1ed1n b\u1eb1ng m\u1ed9t c\u00e1ch nhanh ch\u00f3ng c\u00f3 th\u1ec3 t\u1ea1o ra m\u1ed9t menu \u0111\u1ec3 c\u00f3 th\u1ec3 d\u1ec5 d\u00e0ng \u0111i t\u1eeb sheet n\u00e0y qua sheet kh\u00e1c. \u0110\u1ec3 l\u00e0m \u0111\u01b0\u1ee3c \u0111i\u1ec1u n\u00e0y, ch\u00fang ta s\u1ebd theo t\u1eebng b\u01b0\u1edbc nh\u01b0 h\u01b0\u1edbng d\u1eabn sau c\u1ee7a H\u1ecdc Excel Online<\/strong><\/a>. C\u00f9ng b\u1eaft \u0111\u1ea7u ngay th\u00f4i<\/em><\/p>\n

T\u1ea1o m\u1ee5c l\u1ee5c b\u1ea3ng t\u00ednh trong file Excel<\/h2>\n

\u0110\u1ea7u ti\u00ean, \u1edf sheet b\u1ea1n mu\u1ed1n t\u1ea1o m\u1ee5c l\u1ee5c, t\u1ea1o 1 b\u1ea3ng t\u00ednh theo c\u1ea5u tr\u00fac nh\u01b0 sau<\/p>\n

\"T\u1ea1o<\/a>
T\u1ea1o m\u1ee5c l\u1ee5c trong b\u1ea3ng t\u00ednh excel<\/figcaption><\/figure>\n

Gi\u1ea3 s\u1eed b\u1ea1n c\u00f3 10 sheets th\u00ec \u1edf \u0111\u00e2y ch\u00fang ta s\u1ebd \u0111\u00e1nh s\u1ed1 cho c\u1ed9t Index t\u1eeb 1 t\u1edbi 10. Sau \u0111\u00f3, ch\u00fang ta s\u1ebd c\u1ea7n t\u1ea1o 1 Name Range<\/a> b\u1eb1ng c\u00e1ch b\u1ea5m v\u00e0o th\u1ebb Formulas <\/strong>> Define Name<\/strong><\/p>\n

\"T\u1ea1o<\/a>
T\u1ea1o Name Range<\/figcaption><\/figure>\n

Trong c\u1eeda s\u1ed5 New Name, b\u1ea1n h\u00e3y thi\u1ebft l\u1eadp nh\u01b0 sau:<\/p>\n

Name: SheetNames<\/strong><\/p>\n

Refers to:<\/p>\n

<\/code>=REPLACE(GET.WORKBOOK(1),1,FIND(\"]\",GET.WORKBOOK(1)),\"\")<\/code><\/p>\n

\"Thi\u1ebft<\/a>
Thi\u1ebft l\u1eadp Name Range<\/figcaption><\/figure>\n

Ti\u1ebfp theo, b\u1ea1n s\u1ebd c\u1ea7n thi\u1ebft l\u1eadp c\u00f4ng th\u1ee9c trong \u00f4 B2 v\u00e0 l\u1eb7p l\u1ea1i cho c\u00e1c \u00f4 ph\u00eda d\u01b0\u1edbi c\u1ee7a b\u1ea3ng t\u00ednh nh\u01b0 sau:<\/p>\n

\n
\n
\n
\n<\/a>\n<\/div>\n<\/div>\n

=INDEX(SheetNames,A2)<\/code><\/p>\n

\"Thi\u1ebft<\/a>
Thi\u1ebft l\u1eadp c\u00f4ng th\u1ee9c v\u1edbi Name Range<\/figcaption><\/figure>\n

Nh\u01b0 v\u1eady, t\u1edbi \u0111\u00e2y, b\u1ea1n \u0111\u00e3 t\u1ea1o m\u1ee5c l\u1ee5c t\u00ean c\u1ee7a c\u00e1c sheet trong file Excel r\u1ed3i. B\u00e2y gi\u1edd, ch\u00fang ta s\u1ebd c\u00f9ng nhau \u0111i th\u00eam Hyperlink cho c\u00e1c b\u1ea3ng t\u00ednh n\u00e0y b\u1eb1ng c\u00e1ch s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c sau \u0111\u00e2y t\u1eeb \u00f4 C2:<\/p>\n

=HYPERLINK(\"#\" & B2 & \"!A1\",\"Go to\")<\/code><\/p>\n

\"T\u1ea1o<\/a>
T\u1ea1o Hyperlink trong b\u1ea3ng t\u00ednh \u0111\u1ebfn sheet kh\u00e1c<\/figcaption><\/figure>\n

Trong v\u00ed d\u1ee5 tr\u00ean th\u00ec m\u1ee5c l\u1ee5c \u0111\u01b0\u1ee3c t\u1ea1o ra n\u1eb1m tr\u00ean Sheet1, n\u1ebfu c\u00e1c b\u1ea1n mu\u1ed1n \u1edf m\u1ed7i b\u1ea3ng t\u00ednh c\u00f3 n\u00fat \u201cQuay v\u1ec1\u201d \u0111\u1ec3 khi click v\u00e0o s\u1ebd tr\u1edf v\u1ec1 Sheet1, th\u00ec c\u00e1c b\u1ea1n c\u00f3 th\u1ec3 th\u00eam l\u1ea7n l\u01b0\u1ee3t v\u00e0o c\u00e1c b\u1ea3ng t\u00ednh \u0111\u00f3 t\u1ea1i \u00f4 mong mu\u1ed1n c\u00f4ng th\u1ee9c sau \u0111\u00e2y:<\/p>\n

=HYPERLINK(\"#Sheet1!A1\",\"Quay v\u1ec1\")<\/code><\/p>\n

B\u1ea1n ch\u1ec9 c\u1ea7n vi\u1ebft c\u00f4ng th\u1ee9c n\u00e0y 1 l\u1ea7n, sau \u0111\u00f3 c\u00f3 th\u1ec3 copy qua c\u00e1c sheet kh\u00e1c \u0111\u1ec3 ti\u1ebft ki\u1ec7m th\u1eddi gian.<\/p>\n

N\u1ebfu t\u00ean sheet c\u1ee7a c\u00e1c b\u1ea1n c\u00f3 d\u1ea5u c\u00e1ch, th\u00ec h\u00e0m Hyperlink c\u1ea7n \u0111\u01b0\u1ee3c vi\u1ebft nh\u01b0 sau:
\n=HYPERLINK(\"#'Co dau cach'!A1\",\"Quay v\u1ec1\")<\/code><\/p>\n

Khi t\u00ean sheet \u0111\u1eb7t trong 1 \u00f4 kh\u00e1c th\u00ec c\u00e1ch n\u1ed1i chu\u1ed7i trong h\u00e0m Hyperlink nh\u01b0 sau:<\/p>\n

=HYPERLINK(\"#'\" & B2 & \"'!A1\",\"Quay v\u1ec1\")<\/code><\/p>\n

(Gi\u1ea3 s\u1eed B2 l\u00e0 \u00f4 ch\u1ee9a t\u00ean sheet)<\/p>\n

V\u00e0 cu\u1ed1i c\u00f9ng, v\u00ec trong Name Range ch\u00fang ta s\u1eed d\u1ee5ng h\u00e0m Macro 4, n\u00ean ch\u00fang ta s\u1ebd l\u01b0u file Excel n\u00e0y l\u1ea1i d\u01b0\u1edbi d\u1ea1ng file c\u00f3 h\u1ed7 tr\u1ee3 Macros \u0111\u1ec3 l\u1ea7n sau m\u1edf l\u00ean th\u00ec thi\u1ebft l\u1eadp m\u1ee5c l\u1ee5c c\u1ee7a ch\u00fang ta v\u1eabn s\u1ebd ho\u1ea1t \u0111\u1ed9ng b\u1eb1ng c\u00e1ch b\u1ea5m ph\u00edm F12 tr\u00ean b\u00e0n ph\u00edm v\u00e0 trong c\u1eeda s\u1ed5 Save As \u2026, ph\u1ea7n Save As Type, c\u00e1c b\u1ea1n c\u1ea7n ch\u1ecdn l\u00e0 Excel Macro-Enabled Workbook.<\/p>\n

Video tham kh\u1ea3o<\/a>:<\/p>\n