{"id":9093,"date":"2021-08-29T09:19:25","date_gmt":"2021-08-29T02:19:25","guid":{"rendered":"https:\/\/tct.vn\/blog\/?p=9093"},"modified":"2021-08-29T09:19:25","modified_gmt":"2021-08-29T02:19:25","slug":"tim-kiem-gan-dung-trong-google-sheets","status":"publish","type":"post","link":"https:\/\/tct.vn\/blog\/tim-kiem-gan-dung-trong-google-sheets\/","title":{"rendered":"T\u00ecm ki\u1ebfm g\u1ea7n \u0111\u00fang trong Google Sheets"},"content":{"rendered":"

D\u1eef li\u1ec7u \u0111\u1ea7u v\u00e0o c\u1ee7a v\u00ed d\u1ee5<\/h2>\n

D\u1eef li\u1ec7u \u0111\u1ea7u v\u00e0o c\u1ee7a v\u00ed d\u1ee5 l\u00e0 t\u00ean c\u00e1c kh\u00f3a h\u1ecdc c\u1ee7a Thanh, v\u00e0 2 c\u1ed9t d\u1eef li\u1ec7u CourseId v\u00e0 Assistant ch\u00fang ta \u0111i\u1ec1n th\u00eam v\u00e0o \u0111\u1ec3 minh h\u1ecda cho ch\u1ee9c n\u0103ng t\u00ecm ki\u1ebfm ch\u00fang ta s\u1ebd vi\u1ebft.<\/p>\n

\"cach-viet-cong-thuc-tim-kiem-gan-dung-cho-nhieu-cot-trong-google-sheets\"<\/p>\n

<\/span>X\u00e2y d\u1ef1ng t\u00ednh to\u00e1n ph\u1ea7n tr\u0103m \u0111\u1ed9 kh\u1edbp<\/h2>\n

<\/span>N\u1ed1i c\u00e1c c\u1ed9t c\u1ea7n t\u00ecm ki\u1ebfm<\/h3>\n

\u00dd t\u01b0\u1edfng c\u1ee7a ch\u00fang ta \u1edf \u0111\u00e2y s\u1ebd l\u00e0: nh\u1eadp m\u1ed9t s\u1ed1 t\u1eeb v\u00e0o \u00f4 t\u00ecm ki\u1ebfm, v\u00e0 c\u00f4ng th\u1ee9c s\u1ebd c\u1ea7n \u0111\u00e1nh gi\u00e1 xem bao nhi\u00eau t\u1eeb trong \u00f4 t\u00ecm ki\u1ebfm \u0111\u00f3 c\u00f3 th\u1ec3 xu\u1ea5t hi\u1ec7n \u1edf b\u1ea5t c\u1ee9 d\u00f2ng d\u1eef li\u1ec7u n\u00e0o c\u1ee7a ch\u00fang ta. \u0110\u1ec3 t\u00ecm ki\u1ebfm \u0111\u01b0\u1ee3c \u1edf nhi\u1ec1u c\u1ed9t, ch\u00fang ta s\u1ebd \u0111i n\u1ed1i c\u00e1c c\u1ed9t v\u1edbi nhau \u0111\u1ec3 t\u1ea1o th\u00e0nh m\u1ed9t c\u1ed9t ph\u1ee5<\/p>\n

=JOIN<\/span>(\" \"<\/span>,A2<\/span>:C2<\/span>)<\/code><\/pre>\n
<\/span>T\u00e1ch chu\u1ed7i t\u00ecm ki\u1ebfm ra th\u00e0nh t\u1eebng t\u1eeb b\u1edfi d\u1ea5u c\u00e1ch<\/h3>\n
\u1ede \u0111\u00e2y, v\u00ed d\u1ee5 ch\u00fang ta s\u1ebd c\u00f3 m\u1ed9t chu\u1ed7i t\u00ecm ki\u1ebfm l\u00e0\u00a0sap c\u01a1 b\u1ea3n vba\u00a0<\/em>, chu\u1ed7i n\u00e0y g\u1ed3m c\u00f3 4 t\u1eeb, ch\u00fang ta s\u1ebd s\u1eed d\u1ee5ng h\u00e0m Split \u0111\u1ec3 c\u00f3 th\u1ec3 t\u00e1ch 4 t\u1eeb n\u00e0y ra nh\u01b0 sau v\u1edbi gi\u00e1 s\u1eed c\u1ee5m t\u1eeb n\u00e0y \u0111\u01b0\u1ee3c l\u01b0u trong \u00f4 B2:<\/p>\n
=SPLIT<\/span>(B2<\/span>, \" \"<\/span>)<\/code><\/pre>\n
<\/span>T\u00ecm ki\u1ebfm t\u1eebng t\u1eeb t\u00e1ch ra trong chu\u1ed7i t\u00ecm ki\u1ebfm<\/h3>\n
Sau khi t\u00e1ch \u0111\u01b0\u1ee3c c\u00e1c t\u1eeb trong chu\u1ed7i t\u00ecm ki\u1ebfm ra, ch\u00fang ta s\u1ebd k\u1ebft h\u1ee3p c\u00f4ng th\u1ee9c nh\u01b0 sau \u0111\u1ec3 t\u00ecm ki\u1ebfm t\u1eebng t\u1eeb n\u00e0y c\u00f3 trong \u201cc\u1ed9t ph\u1ee5\u201d \u2013 khi ch\u00fang ta n\u1ed1i 3 c\u1ed9t A, B, C v\u00e0o hay kh\u00f4ng b\u1eb1ng c\u00f4ng th\u1ee9c nh\u01b0 sau:<\/p>\n
=MATCH<\/span>(\"*\"<\/span> & split<\/span>(B2<\/span>,\" \"<\/span>) & \"*\"<\/span>,join<\/span>(\" \"<\/span>, A2<\/span>:C2<\/span>),0<\/span>)<\/code><\/pre>\n
N\u1ebfu ch\u00fang ta s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c n\u00e0y, th\u00ec k\u1ebft qu\u1ea3 ch\u1ec9 \u0111\u01b0\u1ee3c ghi v\u00e0o m\u1ed9t \u00f4, v\u1eady n\u00ean, ch\u00fang ta s\u1ebd bao ra ngo\u00e0i c\u00f4ng th\u1ee9c tr\u00ean v\u1edbi h\u00e0m ArrayFormula:<\/p>\n
\n
<\/a><\/div>\n<\/div>\n
=ArrayFormula<\/span>(MATCH<\/span>(\"*\"<\/span> & split<\/span>(B2<\/span>,\" \"<\/span>) & \"*\"<\/span>,join<\/span>(\" \"<\/span>, A2<\/span>:C2<\/span>),0<\/span>))<\/code><\/pre>\n
Trong c\u00f4ng th\u1ee9c tr\u00ean, s\u1ebd xu\u1ea5t hi\u1ec7n \u1edf v\u00f9ng k\u1ebft qu\u1ea3 nh\u1eefng l\u1ed7i\u00a0#NA<\/code>\u00a0b\u1edfi v\u00ec c\u00f3 nh\u1eefng t\u1eeb trong chu\u1ed7i t\u00ecm ki\u1ebfm s\u1ebd kh\u00f4ng \u0111\u01b0\u1ee3c t\u00ecm th\u1ea5y trong v\u00f9ng\u00a0A2:C2<\/code>, ch\u00fang ta s\u1ebd chuy\u1ec3n nh\u1eefng l\u1ed7i n\u00e0y th\u00e0nh s\u1ed1 0 b\u1eb1ng c\u00e1ch s\u1eed d\u1ee5ng h\u00e0m IFERROR nh\u01b0 sau:<\/p>\n
=ArrayFormula<\/span>(IFERROR<\/span>(MATCH<\/span>(\"*\"<\/span> & split<\/span>(B2<\/span>,\" \"<\/span>) & \"*\"<\/span>,join<\/span>(\" \"<\/span>, A2<\/span>:C2<\/span>),0<\/span>),0<\/span>))<\/code><\/pre>\n
<\/span>T\u00ednh to\u00e1n t\u1ef7 l\u1ec7 kh\u1edbp<\/h3>\n
Sau khi \u0111\u00e3 c\u00f3 \u0111\u01b0\u1ee3c m\u1ed9t d\u00e3y k\u1ebft qu\u1ea3 t\u1eeb c\u00f4ng th\u1ee9c tr\u00ean, ch\u00fang ta s\u1ebd ngh\u0129 \u0111\u1ebfn vi\u1ec7c l\u00e0m th\u1ebf n\u00e0o \u0111\u1ec3 t\u00ednh to\u00e1n \u0111\u01b0\u1ee3c m\u1ed9t t\u1ef7 l\u1ec7 gi\u1eefa s\u1ed1 t\u1eeb trong chu\u1ed7i t\u00ecm ki\u1ebfm \u0111\u01b0\u1ee3c t\u00ecm th\u1ea5y trong c\u1ed9t ph\u1ee5 v\u1edbi t\u1ed5ng s\u1ed1 t\u1eeb \u0111ang \u0111\u01b0\u1ee3c nh\u1eadp v\u00e0o \u00f4 t\u00ecm ki\u1ebfm. \u0110\u1ec3 t\u00ednh s\u1ed1 t\u1eeb \u0111\u01b0\u1ee3c nh\u1eadp v\u00e0o \u00f4 t\u00ecm ki\u1ebfm, ch\u00fang ta c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c nh\u01b0 sau:<\/p>\n
=COUNTA<\/span>(SPLIT<\/span>(B2<\/span>,\" \"<\/span>)<\/code><\/pre>\n
T\u1ed5ng s\u1ed1 t\u1eeb \u0111\u00e3 kh\u1edbp c\u00f3 th\u1ec3 \u0111\u01b0\u1ee3c t\u00ednh b\u1eb1ng c\u00f4ng th\u1ee9c:<\/p>\n
=ArrayFormula<\/span>(SUM<\/span>(IFERROR<\/span>(MATCH<\/span>(\"*\"<\/span> & split<\/span>(B2<\/span>,\" \"<\/span>) & \"*\"<\/span>,join<\/span>(\" \"<\/span>, A2<\/span>:C2<\/span>),0<\/span>),0<\/span>)))<\/code><\/pre>\n
Ch\u00fang ta t\u00ednh to\u00e1n ra s\u1ed1 t\u1eeb \u0111\u01b0\u1ee3c nh\u1eadp v\u00e0o \u00f4 t\u00ecm ki\u1ebfm nh\u01b0 sau:<\/p>\n
=COUNTA<\/span>(SPLIT<\/span>(B2<\/span>,\" \"<\/span>)<\/code><\/pre>\n
V\u00e0 sau \u0111\u00f3, ch\u00fang ta s\u1ebd t\u00ednh ra t\u1ef7 l\u1ec7 kh\u1edbp nh\u01b0 sau:<\/p>\n
\r\n=ArrayFormula<\/span>(\r\n   SUM<\/span>(\r\n     IFERROR<\/span>(\r\n       MATCH<\/span>(\"*\"<\/span> & split<\/span>(B2<\/span>,\" \"<\/span>) & \"*\"<\/span>,join<\/span>(\" \"<\/span>, A2<\/span>:C2<\/span>),0<\/span>)\r\n       ,0<\/span>))\r\n   ) \/ counta<\/span>(split<\/span>(B2<\/span>,\" \"<\/span>)\r\n)\r\n<\/code><\/pre>\n
<\/span>Thi\u1ebft l\u1eadp c\u00f4ng th\u1ee9c FILTER \u0111\u1ec3 tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 t\u00ecm ki\u1ebfm<\/h3>\n
\"cach-viet-cong-thuc-tim-kiem-gan-dung-cho-nhieu-cot-trong-google-sheets-01\"<\/p>\n
Sau khi ch\u00fang ta \u0111\u00e3 c\u00f3 m\u1ed9tc\u1ed9t ph\u1ee5 l\u00e0 t\u1ef7 l\u1ec7 kh\u1edbp gi\u1eefa chu\u1ed7i t\u00ecm ki\u1ebfm v\u00e0 k\u1ebft qu\u1ea3 t\u00ecm ki\u1ebfm, ch\u00fang ta s\u1ebd thi\u1ebft l\u1eadp c\u00f4ng th\u1ee9c \u0111\u1ec3 c\u00f3 th\u1ec3 tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 n\u00e0y khi g\u00f5 v\u00e0o \u00f4 t\u00ecm ki\u1ebfm nh\u01b0 sau:<\/p>\n
=FILTER<\/span>(Sheet1!A1<\/span>:D,Sheet1!D:D<\/span>>0<\/span>,Sheet1!D:D<\/span>>B1<\/span>)<\/code><\/pre>\n
Trong c\u00f4ng th\u1ee9c tr\u00ean: D\u1eef li\u1ec7u ngu\u1ed3n \u1edf Sheet1, c\u1ed9t A, B, C; c\u1ed9t D l\u00e0 c\u1ed9t ch\u1ee9a t\u1ef7 l\u1ec7 kh\u1edbp.<\/p>\n","protected":false},"excerpt":{"rendered":"
D\u1eef li\u1ec7u \u0111\u1ea7u v\u00e0o c\u1ee7a v\u00ed d\u1ee5 D\u1eef li\u1ec7u \u0111\u1ea7u v\u00e0o c\u1ee7a v\u00ed d\u1ee5 l\u00e0 t\u00ean c\u00e1c kh\u00f3a h\u1ecdc c\u1ee7a Thanh, v\u00e0 2 c\u1ed9t d\u1eef li\u1ec7u CourseId v\u00e0 Assistant ch\u00fang ta \u0111i\u1ec1n th\u00eam v\u00e0o \u0111\u1ec3 minh h\u1ecda cho ch\u1ee9c n\u0103ng t\u00ecm ki\u1ebfm ch\u00fang ta s\u1ebd vi\u1ebft. X\u00e2y d\u1ef1ng t\u00ednh to\u00e1n ph\u1ea7n tr\u0103m \u0111\u1ed9 kh\u1edbp N\u1ed1i […]<\/p>\n","protected":false},"author":3,"featured_media":9094,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16],"tags":[],"class_list":["post-9093","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tin-tuc"],"_links":{"self":[{"href":"https:\/\/tct.vn\/blog\/wp-json\/wp\/v2\/posts\/9093","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=9093"}],"version-history":[{"count":0,"href":"https:\/\/tct.vn\/blog\/wp-json\/wp\/v2\/posts\/9093\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/tct.vn\/blog\/wp-json\/wp\/v2\/media\/9094"}],"wp:attachment":[{"href":"https:\/\/tct.vn\/blog\/wp-json\/wp\/v2\/media?parent=9093"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tct.vn\/blog\/wp-json\/wp\/v2\/categories?post=9093"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tct.vn\/blog\/wp-json\/wp\/v2\/tags?post=9093"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}