{"id":12688,"date":"2025-02-20T19:19:22","date_gmt":"2025-02-20T19:19:22","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2025\/02\/20\/thu-dung-ai-deepseek-giup-viet-ham-trong-google-sheets-excel\/"},"modified":"2025-02-20T19:19:22","modified_gmt":"2025-02-20T19:19:22","slug":"thu-dung-ai-deepseek-giup-viet-ham-trong-google-sheets-excel","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2025\/02\/20\/thu-dung-ai-deepseek-giup-viet-ham-trong-google-sheets-excel\/","title":{"rendered":"Th\u1eed d\u00f9ng AI Deepseek gi\u00fap vi\u1ebft h\u00e0m trong Google Sheets\/Excel"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nO_m37Xw#1\">\n<p>T\u1eeb khi l\u00e0n s\u00f3ng AI b\u00f9ng n\u1ed5 v\u1edbi chatGPT, ch\u1eafc h\u1eb3n ai c\u0169ng \u0111\u00e3 t\u1eebng th\u1eed d\u00f9ng AI cho m\u1ed9t t\u00e1c v\u1ee5 n\u00e0o \u0111\u00f3 trong \u0111\u1eddi s\u1ed1ng r\u1ed3i ph\u1ea3i kh\u00f4ng? Trong b\u00e0i vi\u1ebft n\u00e0y, H\u1ecdc Excel Online s\u1ebd th\u1eed d\u00f9ng AI Deepseek gi\u00fap vi\u1ebft h\u00e0m trong Google Sheets nh\u00e9.<\/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-67b78038ec2be\" 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-1\"><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/blog.hocexcel.online\/deepseek-giup-viet-ham.html#AI_Deepseek_giup_viet_ham\" title=\"AI Deepseek gi\u00fap vi\u1ebft h\u00e0m\">AI Deepseek gi\u00fap vi\u1ebft h\u00e0m<\/a>\n<ul class=\"ez-toc-list-level-2\">\n<li class=\"ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/blog.hocexcel.online\/deepseek-giup-viet-ham.html#Bai_toan_Tao_thanh_tra_cuu_trong_Google_Sheets\" title=\"B\u00e0i to\u00e1n: T\u1ea1o thanh tra c\u1ee9u trong Google Sheets \">B\u00e0i to\u00e1n: T\u1ea1o thanh tra c\u1ee9u trong Google Sheets <\/a><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h1 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"AI_Deepseek_giup_viet_ham\"><\/span>AI Deepseek gi\u00fap vi\u1ebft h\u00e0m<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<p>N\u1ebfu c\u00e1c b\u1ea1n v\u1eabn c\u00f2n l\u1ea1 l\u1eabm v\u1edbi c\u00e1i t\u00ean n\u00e0y th\u00ec Deepseek l\u00e0 m\u1ed9t m\u00f4 h\u00ecnh AI \u0111\u1ebfn t\u1eeb trung qu\u1ed1c, \u0111\u01b0\u1ee3c ra m\u1eaft v\u00e0 g\u00e2y ch\u00fa \u00fd v\u00e0o cu\u1ed1i n\u0103m 2024 b\u1edfi kh\u1ea3 n\u0103ng suy lu\u1eadn tr\u01b0\u1edbc khi tr\u1ea3 l\u1eddi \u0111\u01b0\u1ee3c t\u00edch h\u1ee3p v\u00e0o m\u00f4 h\u00ecnh R1.  N\u00f3i m\u1ed9t c\u00e1ch \u0111\u01a1n gi\u1ea3n, thay v\u00ec \u0111\u01b0a ra c\u00e2u tr\u1ea3 l\u1eddi ngay, AI s\u1ebd \u201csuy ngh\u0129\u201d tr\u01b0\u1edbc, s\u1ebd \u0111\u01b0a ra c\u00e1c tr\u01b0\u1eddng h\u1ee3p, t\u1ef1 ph\u1ea3n bi\u1ec7n l\u1ea1i ch\u00ednh c\u00e2u tr\u1ea3 l\u1eddi c\u1ee7a m\u00ecnh\u2026 cho \u0111\u1ebfn khi \u0111\u01b0a ra ph\u01b0\u01a1ng \u00e1n h\u1ee3p l\u00fd nh\u1ea5t cho ng\u01b0\u1eddi d\u00f9ng. \u1ee8ng d\u1ee5ng \u0111i\u1ec1u n\u00e0y trong th\u1ef1c ti\u1ec5n, ch\u00fang ta s\u1ebd c\u00f3 m\u1ed9t \u201cng\u01b0\u1eddi\u201d ngh\u0129 c\u00f9ng, \u0111\u01b0a ra c\u00e1c ph\u01b0\u01a1ng \u00e1n tr\u01b0\u1edbc khi c\u00f3 gi\u1ea3i ph\u00e1p. H\u00e3y c\u00f9ng xem th\u1eed b\u00e0i to\u00e1n d\u01b0\u1edbi \u0111\u00e2y:<\/p>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Bai_toan_Tao_thanh_tra_cuu_trong_Google_Sheets\"><\/span>B\u00e0i to\u00e1n: T\u1ea1o thanh tra c\u1ee9u trong Google Sheets <span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>M\u00ecnh c\u00f3 m\u1ed9t b\u1ea3ng nh\u01b0 sau:<\/p>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"753\" height=\"196\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/02\/image.png\" class=\"wp-image-44222\" title=\"image\"><\/figure>\n<\/div>\n<p>B\u00e0i to\u00e1n \u1edf \u0111\u00e2y \u0111\u00f3 l\u00e0 t\u1ea1o m\u1ed9t thanh t\u00ecm ki\u1ebfm gi\u00fap nhanh ch\u00f3ng l\u1ecdc d\u1eef li\u1ec7u theo c\u00e1c \u0111i\u1ec1u ki\u1ec7n v\u00e0 hi\u1ec3n th\u1ecb. V\u00ed d\u1ee5, n\u1ebfu nh\u1eadp v\u00e0o thanh t\u00ecm ki\u1ebfm c\u1ee5m t\u1eeb \u201cJenny\u201d, l\u00fac n\u00e0y ch\u1ec9 d\u1eef li\u1ec7u c\u1ee7a d\u00f2ng th\u1ee9 3 \u0111\u01b0\u1ee3c \u0111\u01b0a ra.<\/p>\n<p>\u0110\u1ec3 gi\u1ea3i quy\u1ebft b\u00e0i to\u00e1n n\u00e0y v\u1edbi AI Deepseek, m\u00ecnh \u0111\u01b0a ra nh\u1eefng y\u00eau c\u1ea7u nh\u01b0 sau:<\/p>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"669\" height=\"480\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/02\/image-1.png\" class=\"wp-image-44223\" title=\"image-1\"><\/figure>\n<\/div>\n<p>C\u1ee5 th\u1ec3 prompt c\u1ee7a m\u00ecnh:<\/p>\n<p>-Nh\u1eafc l\u1ea1i c\u1ea5u tr\u00fac b\u1ea3ng: 7 c\u1ed9t, n\u1ed9i dung t\u1eebng c\u1ed9t<\/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\/2025\/02\/ex101_92019-nho.gif\" title=\"ex101_92019-nho\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p>-Y\u00eau c\u1ea7u t\u1ea1o 1 thanh t\u00ecm ki\u1ebfm \u0111\u1ec3 l\u1ecdc d\u1eef li\u1ec7u, v\u00e0 thanh t\u00ecm ki\u1ebfm s\u1ebd s\u1eed d\u1ee5ng cho b\u1ea5t k\u1ef3 c\u1ed9t n\u00e0o trong 5 c\u1ed9t \u0111\u1ea7u ti\u00ean. V\u00ed d\u1ee5, n\u1ebfu ch\u1ecdn c\u1ed9t <strong>Sales<\/strong> v\u00e0 \u0111i\u1ec1n \u201cEast\u201d v\u00e0o thanh t\u00ecm ki\u1ebfm, ch\u1ec9 d\u00f2ng \u0111\u1ea7u ti\u00ean \u0111\u01b0\u1ee3c hi\u1ec3n th\u1ecb. Nh\u01b0ng n\u1ebfu ta ch\u1ecdn \u0111\u1ed3ng th\u1eddi c\u1ed9t <strong>Sales<\/strong> v\u00e0 <strong>Region<\/strong> sau \u0111\u00f3 \u0111i\u1ec1n \u201cEast\u201d v\u00e0o thanh t\u00ecm ki\u1ebfm, ta s\u1ebd nh\u1eadn \u0111\u01b0\u1ee3c k\u1ebft qu\u1ea3 l\u00e0 d\u00f2ng 1, 4 v\u00e0 5.<\/p>\n<p>-Trong tr\u01b0\u1eddng h\u1ee3p kh\u00f4ng c\u1ed9t n\u00e0o \u0111\u01b0\u1ee3c ch\u1ecdn, t\u1ea5t c\u1ea3 d\u1eef li\u1ec7u s\u1ebd \u0111\u01b0\u1ee3c tr\u1ea3 v\u1ec1.<\/p>\n<p>-Thanh t\u00ecm ki\u1ebfm \u0111\u01b0\u1ee3c t\u1ea1o b\u1eb1ng c\u00f4ng th\u1ee9c, kh\u00f4ng s\u1eed d\u1ee5ng t\u1edbi Google Apps Script.<\/p>\n<p>_<\/p>\n<p>D\u01b0\u1edbi \u0111\u00e2y l\u00e0 ph\u1ea3n h\u1ed3i c\u1ee7a AI, th\u1eddi gian suy ngh\u0129 253 gi\u00e2y:<\/p>\n<div class=\"wp-block-cover\"><span aria-hidden=\"true\" class=\"wp-block-cover__background has-background-dim\"><\/span><\/p>\n<div class=\"wp-block-cover__inner-container is-layout-flow wp-block-cover-is-layout-flow\">\n<div class=\"wp-block-group is-style-default is-layout-flow wp-block-group-is-layout-flow\">\n<div class=\"wp-block-group__inner-container\">\n<p>Okay, let\u2019s see. The user has a Google Sheets setup with a table of sales data and wants to create a search bar in another sheet that can filter the data based on input criteria. The search should check the first five columns (ID, Date, Sales, Region, Unit) but only those that are selected via checkboxes. If all checkboxes are unchecked, it shows all data. They want to use built-in functions only, no Apps Script.<\/p>\n<p>First, I need to figure out how to create a dynamic filter based on the search term and the checkboxes. The checkboxes are likely linked to cells indicating TRUE (checked) or FALSE (unchecked). The search term is in a specific cell.<\/p>\n<p>The main challenge is to check each row against the search term in the selected columns. For each row, if any of the selected columns contain the search term, that row should be included in the results.<\/p>\n<p>Let\u2019s break it down step by step.<\/p>\n<p>1. **Checkbox Handling**: Each checkbox (probably in cells like B1:B5) corresponds to a column. When a checkbox is checked, that column should be included in the search. For example, if the Region checkbox is checked, the formula should check the Region column for the search term.<\/p>\n<p>2. **Search Term**: The user types the search term into a specific cell, say A1. The formula needs to reference this cell.<\/p>\n<p>3. **Filter Function**: Use the FILTER function to display the results. But the condition for FILTER needs to dynamically check each selected column for the search term.<\/p>\n<p>4. **Dynamic Condition**: For each row, create a condition that checks if the search term exists in any of the selected columns. For each column, if the checkbox is checked, check if the search term matches that column\u2019s data. Then combine these with OR logic.<\/p>\n<p>5. **Handling Data Types**: Some columns are numbers (ID, Unit) and others are text (Date as datetime, Sales, Region). The search term is text, so when searching in numeric columns, we might need to convert them to text for comparison. For example, if the user searches \u201c123\u201d, it should match an ID of 123.<\/p>\n<p>6. **Case Insensitivity**: The search should be case-insensitive. Using LOWER on both the search term and the column data can help with that.<\/p>\n<p>7. **Date Handling**: If the Date column is a datetime, converting it to text might be tricky. Using TEXT(date_column, \u201cyyyy-mm-dd hh:mm:ss\u201d) can standardize the format for comparison.<\/p>\n<p>Putting it all together:<\/p>\n<p>\u2013 For each column (ID, Date, Sales, Region, Unit), check if the checkbox is TRUE.<\/p>\n<p>\u2013 For each checked column, check if the search term (lowercase) is in the lowercase version of the column\u2019s data (converted to text).<\/p>\n<p>\u2013 Combine these checks with OR.<\/p>\n<p>\u2013 Apply this combined condition to the FILTER function to get the matching rows.<\/p>\n<p>So, the formula structure would be something like:<\/p>\n<p>=FILTER(Data!A:G,<\/p>\n<p>( (Checkbox_ID = TRUE) * (LOWER(TO_TEXT(Data!A:A)) = LOWER(search_term)) +<\/p>\n<p>( (Checkbox_Date = TRUE) * (LOWER(TEXT(Data!B:B, \u201cyyyy-mm-dd hh:mm:ss\u201d)) = LOWER(search_term)) ) +<\/p>\n<p>\u2026 and so on for other columns \u2026 &gt; 0 )<\/p>\n<p>But wait, adding the conditions with + acts as an OR because if any is TRUE (1), the sum is greater than 0. However, multiplication (*) is used for AND logic here between the checkbox and the column condition.<\/p>\n<p>But actually, each term is (Checkbox * Condition), which would be 1 if both are TRUE. Summing all those terms gives the number of columns where both the checkbox is checked and the condition is met. If the sum is &gt;=1, then the row is included.<\/p>\n<p>But the user wants to check if the search term is in ANY of the checked columns. So for each row, we check each column if it\u2019s checked and matches the term, then OR those results.<\/p>\n<p>Alternatively, using REGEXMATCH might be another approach, but handling each column separately with checkboxes seems more straightforward.<\/p>\n<p>Another consideration: the Data sheet might have headers, so we should exclude the header row in the filter. So, using A2:G instead of A:G, and adjusting the ranges accordingly.<\/p>\n<p>Also, handling empty search term: if the search term is empty, but checkboxes are checked, should it show all rows? But according to the user\u2019s requirement, if all checkboxes are unchecked, it shows everything. If some are checked and the search term is empty, then it\u2019s equivalent to searching for empty string in those columns. But the user probably wants that if the search term is empty, no filtering is applied, regardless of checkboxes. Hmm, but the user says if all checkboxes are unchecked, show all. If checkboxes are checked and search term is empty, maybe it shows all rows where any of the checked columns are empty? That might not be intended. The user\u2019s requirement is that the search is based on the term and the checkboxes. So if the search term is empty, even with checkboxes checked, perhaps it shows all data. But the original problem states that if all checkboxes are unchecked, it lists all data. So maybe the checkboxes only determine which columns to check, but if the search term is empty, it doesn\u2019t filter. Let me recheck the user\u2019s question.<\/p>\n<p>The user says: \u201cif I uncheck all, there will be no filter and will list all data.\u201d So if all checkboxes are unchecked, regardless of the search term, it shows all. But if any checkboxes are checked, then it filters based on the search term in those columns. Also, if the search term is empty, even with checkboxes checked, it would look for empty strings in those columns. But the user probably wants that when the search term is empty, and checkboxes are checked, it doesn\u2019t filter. However, that\u2019s not explicitly stated.<\/p>\n<p>Assuming that the search only applies when there\u2019s a search term. If the search term cell is empty, then no filtering is done. But the user\u2019s current setup doesn\u2019t mention that. So perhaps the formula needs to account for both the checkboxes and the presence of a search term.<\/p>\n<p>Alternatively, the formula will always check the search term against the checked columns. So if the search term is empty, it would look for rows where any of the checked columns are empty. That might not be desired. To avoid that, we can add a condition that if the search term is empty, it returns all rows (if any checkboxes are checked, but maybe not). Wait, this is getting complicated.<\/p>\n<p>But according to the user\u2019s problem statement: the search bar is a cell where they type the criteria. So when they type \u201cEast\u201d, it checks the columns. If they clear the search bar, it should show all data again. Therefore, if the search term is empty, regardless of checkboxes, it shows all data. That makes sense. So the formula should check that the search term is not empty before applying the filters. If the search term is empty, return all data. If checkboxes are all unchecked, return all data. Otherwise, apply the filters.<\/p>\n<p>So combining these conditions:<\/p>\n<p>If the search term is empty OR all checkboxes are unchecked, show all data. Otherwise, filter based on the search term in checked columns.<\/p>\n<p>So the formula would have a condition that checks:<\/p>\n<p>IF( OR(search_term = \u201c\u201d, AND(all checkboxes are FALSE)),<\/p>\n<p>Data!A2:G,<\/p>\n<p>FILTER(Data!A2:G, [conditions based on checkboxes and search term]) )<\/p>\n<p>But how to implement this with built-in functions?<\/p>\n<p>Alternatively, structure the FILTER\u2019s condition to account for these cases.<\/p>\n<p>But perhaps using a helper formula to determine whether to apply the filter or not.<\/p>\n<p>But let\u2019s proceed step by step.<\/p>\n<p>Let\u2019s assume:<\/p>\n<p>\u2013 Search term is in cell A1 of the second sheet.<\/p>\n<p>\u2013 Checkboxes for the five columns (ID, Date, Sales, Region, Unit) are in cells B1:B5, with B1=ID, B2=Date, etc., each linked to a checkbox, so B1 is TRUE when checked.<\/p>\n<p>\u2013 The data is in Data!A2:G (assuming row 1 is headers).<\/p>\n<p>The formula needs to:<\/p>\n<p>1. If A1 is empty, return all data.<\/p>\n<p>2. If all checkboxes (B1:B5) are FALSE, return all data.<\/p>\n<p>3. Else, for each row in Data, check if any of the checked columns contain the search term (exact match? or partial? The user example shows \u201cEast\u201d matching Region=East or Sales=East, which is exact. But maybe it\u2019s better to allow partial matches. Wait, the user example says if you type \u201cEast\u201d it lists records where Region = East or Sales = East. So exact match. But maybe the user wants partial matches. The question isn\u2019t clear. Let\u2019s assume exact match for now, but if the user wants partial, we can adjust with SEARCH or FIND functions.)<\/p>\n<p>But the user\u2019s example uses \u201cEast\u201d to match Region = East. So exact match. So we need to check if the cell\u2019s content equals the search term.<\/p>\n<p>But considering case insensitivity, \u201ceast\u201d should also match \u201cEast\u201d. So convert both to lowercase.<\/p>\n<p>So, for each column:<\/p>\n<p>If the checkbox is TRUE, check if LOWER(Data!column) = LOWER(search_term).<\/p>\n<p>But for columns like Date (which is a datetime), converting to text might be necessary. For example, if the user types \u201c2023-10-05\u201d, it should match the date in the Date column. So converting the date to a text string in a specific format would help. Using TEXT(Data!B2, \u201cyyyy-mm-dd hh:mm:ss\u201d) to standardize the date format.<\/p>\n<p>Similarly, numeric columns like ID and Unit should be converted to text for comparison. For example, ID 123 as text \u201c123\u201d.<\/p>\n<p>So, the formula for each column\u2019s condition would be:<\/p>\n<p>For ID (column A):<\/p>\n<p>IF(B1=TRUE, LOWER(TO_TEXT(Data!A2:A)) = LOWER($A$1), FALSE)<\/p>\n<p>Similarly for Date (column B):<\/p>\n<p>IF(B2=TRUE, LOWER(TEXT(Data!B2:B, \u201cyyyy-mm-dd hh:mm:ss\u201d)) = LOWER($A$1), FALSE)<\/p>\n<p>For Sales (column C):<\/p>\n<p>IF(B3=TRUE, LOWER(Data!C2:C) = LOWER($A$1), FALSE)<\/p>\n<p>For Region (column D):<\/p>\n<p>IF(B4=TRUE, LOWER(Data!D2:D) = LOWER($A$1), FALSE)<\/p>\n<p>For Unit (column E):<\/p>\n<p>IF(B5=TRUE, LOWER(TO_TEXT(Data!E2:E)) = LOWER($A$1), FALSE)<\/p>\n<p>Then, combine these five conditions with OR logic. In Google Sheets, addition of the conditions (since each is 0 or 1) would act as OR. So:<\/p>\n<p>(condition_ID + condition_Date + condition_Sales + condition_Region + condition_Unit) &gt; 0<\/p>\n<p>So the overall FILTER condition would be:<\/p>\n<p>=IF(OR($A$1=\u201d\u201d, COUNTA(B1:B5)=0), Data!A2:G,<\/p>\n<p>FILTER(Data!A2:G,<\/p>\n<p>(IF(B1=TRUE, LOWER(TO_TEXT(Data!A2:A)) = LOWER($A$1), 0) +<\/p>\n<p>(IF(B2=TRUE, LOWER(TEXT(Data!B2:B, \u201cyyyy-mm-dd hh:mm:ss\u201d)) = LOWER($A$1), 0) +<\/p>\n<p>(IF(B3=TRUE, LOWER(Data!C2:C) = LOWER($A$1), 0) +<\/p>\n<p>(IF(B4=TRUE, LOWER(Data!D2:D) = LOWER($A$1), 0) +<\/p>\n<p>(IF(B5=TRUE, LOWER(TO_TEXT(Data!E2:E)) = LOWER($A$1), 0)) &gt; 0))<\/p>\n<p>But in Google Sheets, you can\u2019t use IF inside FILTER\u2019s condition like that. Instead, you need to use array formulas for each condition.<\/p>\n<p>Wait, perhaps using multiplication:<\/p>\n<p>For each column, the condition is (Checkbox) * (ColumnData = SearchTerm). Then sum all these, and if the sum is &gt;=1, include the row.<\/p>\n<p>But since the checkboxes are in individual cells (B1 to B5), which are single cells (not arrays), we can reference them as B$1, B$2, etc.<\/p>\n<p>Wait, but in an array formula, B$1 is a scalar, so when multiplied by an array, it applies to all elements.<\/p>\n<p>So, let\u2019s construct each column\u2019s condition as:<\/p>\n<p>(B$1) * (LOWER(TO_TEXT(Data!A2:A)) = LOWER($A$1)) +<\/p>\n<p>(B$2) * (LOWER(TEXT(Data!B2:B, \u201cyyyy-mm-dd hh:mm:ss\u201d)) = LOWER($A$1)) +<\/p>\n<p>\u2026 and so on.<\/p>\n<p>But in Google Sheets, you can\u2019t mix array and non-array operations like that without ARRAYFORMULA.<\/p>\n<p>Therefore, wrapping everything in ARRAYFORMULA.<\/p>\n<p>But the FILTER function can handle array conditions.<\/p>\n<p>Alternatively, construct the condition using ARRAYFORMULA.<\/p>\n<p>Wait, let\u2019s try writing the formula step by step.<\/p>\n<p>Assume:<\/p>\n<p>\u2013 Search term is in cell A1 of the second sheet.<\/p>\n<p>\u2013 Checkboxes are in B1 (ID), B2 (Date), B3 (Sales), B4 (Region), B5 (Unit).<\/p>\n<p>The formula in the second sheet where results should appear (starting from A2, for example):<\/p>\n<p>=IF(OR(A1=\u201d\u201d, AND(B1:B5=FALSE)), Data!A2:G,<\/p>\n<p>FILTER(Data!A2:G,<\/p>\n<p>ARRAYFORMULA(<\/p>\n<p>(B1 * (LOWER(TO_TEXT(Data!A2:A)) = LOWER(A1))) +<\/p>\n<p>(B2 * (LOWER(TEXT(Data!B2:B, \u201cyyyy-mm-dd hh:mm:ss\u201d)) = LOWER(A1)) +<\/p>\n<p>(B3 * (LOWER(Data!C2:C) = LOWER(A1))) +<\/p>\n<p>(B4 * (LOWER(Data!D2:D) = LOWER(A1))) +<\/p>\n<p>(B5 * (LOWER(TO_TEXT(Data!E2:E)) = LOWER(A1)))<\/p>\n<p>&gt; 0))<\/p>\n<p>))<\/p>\n<p>But let\u2019s check this:<\/p>\n<p>Each term like (B1 * \u2026) uses B1 as TRUE (1) or FALSE (0). If B1 is TRUE, then the corresponding column\u2019s condition is checked. Each condition is an array (per row) comparing the column\u2019s data to the search term. Multiplying by B1 effectively enables or disables that condition.<\/p>\n<p>Adding all these terms gives, for each row, the number of checked columns where the search term matches. If the sum is &gt;0, the row is included.<\/p>\n<p>This should work. However, there are some potential issues:<\/p>\n<p>1. **Date Handling**: If the Date column (B) is a valid date, TEXT(Data!B2:B, \u201cyyyy-mm-dd hh:mm:ss\u201d) converts it to a string. If the user enters a date in a different format, like \u201c10\/05\/2023\u201d, it won\u2019t match. So the user must input the date in the exact format used in the TEXT function. Alternatively, use different formats, but that complicates things. The user needs to be aware of the expected format.<\/p>\n<p>2. **Numeric to Text Conversion**: For ID and Unit columns, converting numbers to text ensures that searching for \u201c123\u201d matches the numeric 123. However, if the user inputs a number with decimals or formatting (like \u201c123.00\u201d), it might not match. Using TO_TEXT should handle integers correctly.<\/p>\n<p>3. **Case Insensitivity**: Converting both the search term and column data to lowercase ensures case insensitivity.<\/p>\n<p>4. **Empty Search Term**: The formula checks if A1 is empty or all checkboxes are unchecked, in which case it returns all data.<\/p>\n<p>But wait, in the initial IF condition, it\u2019s OR(A1=\u201d\u201d, AND(B1:B5=FALSE)). If either the search term is empty OR all checkboxes are unchecked, show all data. But the user\u2019s requirement is that if all checkboxes are unchecked, regardless of the search term, show all data. Also, if the search term is empty, regardless of checkboxes, show all data. That seems to align with the formula.<\/p>\n<p>But wait, the user says: \u201cIf I uncheck all, there will be no filter and will list all data.\u201d So even if there\u2019s a search term, if all checkboxes are unchecked, it lists all data. So the formula correctly includes that in the OR condition.<\/p>\n<p>Another edge case: if some checkboxes are checked, but the search term is empty, then the formula would return all data, because A1 is empty. Which is correct.<\/p>\n<p>Now, putting this into the formula:<\/p>\n<p>=IF(OR(A1=\u201d\u201d, AND(B1:B5=FALSE)), Data!A2:G,<\/p>\n<p>FILTER(Data!A2:G,<\/p>\n<p>ARRAYFORMULA(<\/p>\n<p>(B1 * (LOWER(TO_TEXT(Data!A2:A)) = LOWER(A1)) +<\/p>\n<p>B2 * (LOWER(TEXT(Data!B2:B, \u201cyyyy-mm-dd hh:mm:ss\u201d)) = LOWER(A1)) +<\/p>\n<p>B3 * (LOWER(Data!C2:C) = LOWER(A1)) +<\/p>\n<p>B4 * (LOWER(Data!D2:D) = LOWER(A1)) +<\/p>\n<p>B5 * (LOWER(TO_TEXT(Data!E2:E)) = LOWER(A1))<\/p>\n<p>) &gt;0))<\/p>\n<p>)<\/p>\n<p>But in Google Sheets, the AND function doesn\u2019t work on ranges like AND(B1:B5=FALSE). Instead, we can check if the count of TRUEs is zero. COUNTA(B1:B5) counts the number of checked checkboxes (assuming that when a checkbox is checked, the cell is TRUE). Wait, no: COUNTA counts non-empty cells. If the checkboxes are linked to B1:B5, then when checked, they are TRUE, which is counted by COUNTA. But to count how many are checked, we can use COUNTIF(B1:B5, TRUE). If COUNTIF(\u2026)=0, then all are unchecked.<\/p>\n<p>So the initial condition should be:<\/p>\n<p>IF(OR(A1=\u201d\u201d, COUNTIF(B1:B5, TRUE)=0), Data!A2:G, FILTER(\u2026))<\/p>\n<p>So adjusting the formula:<\/p>\n<p>=IF(OR(A1=\u201d\u201d, COUNTIF(B1:B5, TRUE)=0), Data!A2:G,<\/p>\n<p>FILTER(Data!A2:G,<\/p>\n<p>ARRAYFORMULA(<\/p>\n<p>(B1*(LOWER(TO_TEXT(Data!A2:A))=LOWER(A1)) +<\/p>\n<p>B2*(LOWER(TEXT(Data!B2:B, \u201cyyyy-mm-dd hh:mm:ss\u201d))=LOWER(A1)) +<\/p>\n<p>B3*(LOWER(Data!C2:C)=LOWER(A1) +<\/p>\n<p>B4*(LOWER(Data!D2:D)=LOWER(A1)) +<\/p>\n<p>B5*(LOWER(TO_TEXT(Data!E2:E))=LOWER(A1))<\/p>\n<p>)&gt;0))<\/p>\n<p>)<\/p>\n<p>Wait, but in the formula, the multiplication is between B1 (a single cell) and an array (result of the condition). So when B1 is TRUE (1), it multiplies each element of the condition array by 1, otherwise by 0. Then summing all such terms for each row. If the sum is &gt;0, include the row.<\/p>\n<p>Yes, this should work.<\/p>\n<p>Testing with sample data:<\/p>\n<p>Suppose Data!A2:E has:<\/p>\n<p>ID | Date | Sales | Region | Unit<\/p>\n<p>1 | 2023-10-05 10:00 | Jenny | East | 5<\/p>\n<p>If search term is \u201cEast\u201d, and Region checkbox (B4) is checked, then the condition for Region is TRUE, so the row is included.<\/p>\n<p>If Region is unchecked and Sales is checked, and the search term is \u201cJenny\u201d, it\u2019s included.<\/p>\n<p>If the search term is \u201ceast\u201d (lowercase), with Region checked, it still matches because of LOWER.<\/p>\n<p>Date example: If the user enters \u201c2023-10-05 10:00:00\u201d and Date checkbox is checked, it matches. But if the date is displayed differently, the user must input the exact format.<\/p>\n<p>Potential issues:<\/p>\n<p>\u2013 The date format must match exactly what\u2019s produced by the TEXT function. If the user inputs \u201c05-10-2023\u201d, it won\u2019t match. So the user needs to input dates in \u201cyyyy-mm-dd hh:mm:ss\u201d format.<\/p>\n<p>\u2013 Numeric fields must be input as exact numbers. For example, if the user searches \u201c5\u201d and Unit is 5, it matches. If Unit is 5.0, TO_TEXT might convert to \u201c5\u201d as well, so it should still match.<\/p>\n<p>Another thing: TO_TEXT(Data!A2:A) converts numbers to text, so \u201c00123\u201d would match 123 if the ID is numeric, but if the ID is stored as text with leading zeros, it would matter. So if IDs are numeric, searching for \u201c123\u201d would match the numeric ID 123, but if stored as text \u201c0123\u201d, it wouldn\u2019t. So the user should ensure data types are consistent.<\/p>\n<p>Now, putting all this together in the formula.<\/p>\n<p>Also, the user might have headers in the Data sheet, so the formula should start from row 2. The FILTER function in the formula uses Data!A2:G, assuming data starts at row 2.<\/p>\n<p>Finally, the formula would be:<\/p>\n<p>=IF(OR(A1=\u201d\u201d, COUNTIF(B1:B5, TRUE)=0), Data!A2:G,<\/p>\n<p>FILTER(Data!A2:G,<\/p>\n<p>ARRAYFORMULA(<\/p>\n<p>(B1*(LOWER(TO_TEXT(Data!A2:A))=LOWER(A1)) +<\/p>\n<p>B2*(LOWER(TEXT(Data!B2:B, \u201cyyyy-mm-dd hh:mm:ss\u201d))=LOWER(A1)) +<\/p>\n<p>B3*(LOWER(Data!C2:C)=LOWER(A1)) +<\/p>\n<p>B4*(LOWER(Data!D2:D)=LOWER(A1)) +<\/p>\n<p>B5*(LOWER(TO_TEXT(Data!E2:E))=LOWER(A1))<\/p>\n<p>)&gt;0))<\/p>\n<p>)<\/p>\n<p>Make sure to adjust the ranges (A2:A, B2:B, etc.) to match the actual data range. If the data has 1000 rows, better to use Data!A2:A1000, etc., to avoid processing entire columns which can slow down the sheet.<\/p>\n<p>Alternatively, use Data!A2:G where G is the last column, but again, it\u2019s better to limit the range to the actual data size.<\/p>\n<p>So, the final formula the user should enter in their search results sheet is:<\/p>\n<p>=IF(OR(A1=\u201d\u201d, COUNTIF(B1:B5, TRUE)=0), Data!A2:G,<\/p>\n<p>FILTER(Data!A2:G,<\/p>\n<p>ARRAYFORMULA(<\/p>\n<p>(B1*(LOWER(TO_TEXT(Data!A2:A))=LOWER(A1)) +<\/p>\n<p>B2*(LOWER(TEXT(Data!B2:B, \u201cyyyy-mm-dd hh:mm:ss\u201d))=LOWER(A1)) +<\/p>\n<p>B3*(LOWER(Data!C2:C)=LOWER(A1)) +<\/p>\n<p>B4*(LOWER(Data!D2:D)=LOWER(A1)) +<\/p>\n<p>B5*(LOWER(TO_TEXT(Data!E2:E))=LOWER(A1))<\/p>\n<p>)&gt;0))<\/p>\n<p>)<\/p>\n<p>Note: Ensure that the checkboxes are linked to cells B1 to B5, and the search term is in cell A1. Adjust cell references as needed based on the actual setup.<\/p>\n<p>Another consideration: If the data in the Date column is stored as text, the TEXT function might not be necessary, but if it\u2019s a valid date, the TEXT function is essential to convert it to a comparable string.<\/p>\n<p>Finally, the user may need to adjust the date format in the TEXT function to match how they input dates. For example, if they input dates as \u201cmm\/dd\/yyyy\u201d, the TEXT function should use \u201cmm\/dd\/yyyy hh:mm:ss\u201d instead.<\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<p>Sau \u0111\u00f3, Deepseek \u0111\u00e3 h\u01b0\u1edbng d\u1eabn setup v\u00e0 \u0111\u01b0a cho m\u00ecnh c\u00f4ng th\u1ee9c:<\/p>\n<pre class=\"wp-block-code\"><code>=IF(OR(A1=\"\", COUNTIF(B1:B5, TRUE)=0), Data!A2:G, \nFILTER(Data!A2:G, \n ARRAYFORMULA(\n  (B1*(LOWER(TO_TEXT(Data!A2:A))=LOWER(A1)) +\n  B2*(LOWER(TEXT(Data!B2:B, \"yyyy-mm-dd hh:mm:ss\"))=LOWER(A1)) +\n  B3*(LOWER(Data!C2:C)=LOWER(A1)) +\n  B4*(LOWER(Data!D2:D)=LOWER(A1)) +\n  B5*(LOWER(TO_TEXT(Data!E2:E))=LOWER(A1))\n )&gt;0))\n\n<\/code><\/pre>\n<p>V\u1edbi A1 l\u00e0 n\u01a1i ch\u00fang ta s\u1ebd \u0111\u1eb7t l\u00e0m thanh t\u00ecm ki\u1ebfm, t\u1eeb \u00f4 B1 t\u1edbi B5 s\u1ebd thi\u1ebft l\u1eadp c\u00e1c checkbox t\u01b0\u01a1ng \u1ee9ng v\u1edbi 5 c\u1ed9t ID, Date, Sales, Region v\u00e0 Unit. C\u00f4ng th\u1ee9c s\u1ebd \u0111\u01b0\u1ee3c \u0111\u1eb7t \u1edf 1 \u00f4 ngo\u00e0i nh\u1eefng \u00f4 tr\u00ean, v\u00ed d\u1ee5 nh\u01b0 B8. D\u1eef li\u1ec7u c\u1ee7a ch\u00fang ta n\u1eb1m trong Sheet \u201cData\u201d.<\/p>\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"454\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/02\/image-2-1024x454-1.png\" class=\"wp-image-44224\" title=\"image-2-1024x454-1\"><\/figure>\n<p>Khi th\u1eed ki\u1ec3m tra, c\u00f4ng th\u1ee9c tr\u00ean ch\u1ec9 sai duy nh\u1ea5t m\u1ed9t ch\u1ed7: Data!A2:G. B\u1edfi Google Sheets kh\u00f4ng t\u1ef1 \u0111\u1ed9ng m\u1edf r\u1ed9ng c\u00f4ng th\u1ee9c nh\u01b0 Excel, m\u00e0 ta c\u1ea7n th\u00eam v\u00e0o INDEX ho\u1eb7c ARRAYFORMULA. Sau khi th\u00eam ph\u1ea7n c\u00f2n thi\u1ebfu, c\u00f4ng th\u1ee9c \u0111\u00e3 ho\u1ea1t \u0111\u1ed9ng ho\u00e0n to\u00e0n b\u00ecnh th\u01b0\u1eddng v\u00e0 ch\u00ednh x\u00e1c.<\/p>\n<p>C\u00e1c b\u1ea1n c\u00f3 th\u1ec3 xem v\u00e0 t\u1ea3i v\u1ec1 file t\u1ea1i \u0111\u00e2y: <a href=\"https:\/\/docs.google.com\/spreadsheets\/d\/1MqfL_dT6saBXEj2HRkIkA9HPT61imTMsX6gUF3HTulg\/edit?usp=sharing\">https:\/\/docs.google.com\/spreadsheets\/d\/1MqfL_dT6saBXEj2HRkIkA9HPT61imTMsX6gUF3HTulg\/edit?usp=sharing<\/a><\/p>\n<\/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\/2025\/02\/ex101_92019-nho.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":12689,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-12688","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-co-ban"],"_links":{"self":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/12688","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=12688"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/12688\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/12689"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=12688"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=12688"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=12688"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}