Cách Dùng Hàm Importrange / Top 17 Xem Nhiều Nhất & Mới Nhất 9/2023 # Top Trend

Trên Excel, để trích xuất dữ liệu từ các bảng tính hoặc từng các file Excel thì cần sự hỗ trợ của VBA, nhưng trong Google Sheets thì việc này đơn giản hơn rất nhiều nhờ hàm IMPORTRANGE siêu mạnh. Trong bài viết này chúng ta sẽ tìm hiểu cách sử dụng hàm IMPORTRANGE và cách kết hợp hàm QUERY kết hợp IMPORTRANGE để liên kết dữ liệu giữa các file.

Trước khi tìm hiểu về hàm IMPORTRANGE và hàm QUERY, bạn cần thiết lập cho phép truy cập dữ liệu còn gọi là Allow Access tại các file thực hành, vì nếu không cho phép thì công thức sẽ không hoạt động được.

Cách sử dụng hàm IMPORTRANGE để liên kết dữ liệu Cấu trúc hàm IMPORTRANGE:

Range string: còn gọi là “Chuỗi dải ô” là tên chính xác của bảng tính lấy dữ liệu (Ví dụ Sheet 1, Sheet 2 hoặc được đặt tên là Data 1, Data 2 …), theo sau là ‘!’ và phạm vi của các ô muốn lấy dữ liệu .

Ví dụ ứng dụng hàm IMPORTRANGE như sau:

Ta có bảng dữ liệu:

Trên bảng tính này, chúng ta sẽ lấy dữ liệu từ ô A1 đến D100 trong Sheet1. Do đó ta sẽ có công thức sau:

=ImportRange(“1ByTut9xooZdPIBF55gzQ0Cdi04owDTtLVc_gPGtOKY0”, “Sheet1!A1:D100”)

Hoặc:

Công thức này sẽ nhập dữ liệu từ một bảng tính sang bảng tính khác. Ở đây là nhập dữ liệu từ ô A1 đến ổ D100 từ bảng tính “Sheet1”. Dữ liệu sẽ được hiển thị sang bảng tính khác vẫn giữ nguyên định dạng như trong sheet gốc.

Sử dụng hàm QUERY để nhập dữ liệu có điều kiện

Hàm IMPORTRANGE rất mạnh để liên kết hoặc tổng hợp số lượng lớn dữ liệu giữa các file Google Sheets, nhưng để liên kết dữ liệu cụ thể chính xác theo điều kiện cần trích xuất thì hàm QUERY sẽ mạnh hơn rất nhiều.

Trong ví dụ này, chúng ta sẽ lấy dữ liệu “Units Sold” của Đức.

CHỈ 7 GIỜ HỌC BÀI BẢN, TIẾP KIỆM HÀNG CHỤC NGHÌN GIỜ TRA CỨU

Để lấy dữ liệu gõ công thức sau:

=QUERY( ImportRange( “1ByTut9xooZdPIBF55gzQ0Cdi04owDTtLVc_gPGtOKY0”, “Sheet1!A1:O1000” ) , “select Col5 where Col2 = ‘Germany'”)

Ở đây, dữ liệu “ImportRange” thực hiện theo đúng cú pháp trước và sau đó chúng ta sẽ chỉ định hàm tới cột 5 là cột chứa “Units Sold”) có chứa cột 2 “Germany”. Vì vậy, có hai “đối số” trong truy vấn này là ImportRange và select ColX where ColY = ‘Z’.

Để nắm vững kỹ năng ứng dụng Google Sheets vào công việc, bạn nên tham khảo ngay chương trình

Hàm OR là một hàm logic để kiểm tra nhiều điều kiện cùng một lúc. OR trả về kết quả TRUE hoặc FALSE.

Giả sử:để kiểm tra A2 cho “x” hoặc “y”, hãy sử dụng = OR (A2 = “x”, A2 = “y”). Ngoài ta OR thường xuyên kết hợp với hàm điều kiện IF.

Nghĩa là nếu A2 có giá trị lớn hơn 1 HOẶC nhỏ hơn 100 thì kết quả trả về là TRUE, nếu không kết quả trả về là FALSE.

Công thức hàm OR

=OR(logical1, [logical2], …)

Trong đó

Logical 1: Là một giá trị logic đầu tiên hoặc là điều kiện bắt buộc cho đánh giá

Logical 2: Là một giá trị logic thứ 2, tùy chọn

Sử dụng hàm OR để kiểm tra nhiều điều kiện cùng một lúc, tổng cộng tối đa 255 điều kiện.

Làm sao sử dụng hàm OR trong Excel?

Ở phần này chúng ta sẽ làm ví dụ hàm OR thông thường và kết hợp với hàm IF

Ví dụ 1: Sử dụng hàm OR trong Excel để kiểm tra

Ta có bảng sau, thể hiện số lượng học viên các tháng và số lượng học viên đã hoàn thành khóa học qua các tháng. Bây giờ hãy sử dụng công thức OR để kiểm tra

Ví dụ minh họa cách sử dụng hàm Or trong Excel

Giải thích như sau:

=OR(A2=”Bói Tarot”, A2=”Tự học Yoga”)

Sẽ trả về: TRUE nếu A2 chứa “Bói Tarot” hoặc “Tự học Yoga”, FALSE nếu không có giá trị nào

Ví dụ minh họa cách sử dụng hàm Or trong Excel Ví dụ minh họa cách sử dụng hàm Or trong Excel

Sẽ trả về: TRUE nếu B2 lớn hơn hoặc bằng 1000 hoặc C2 lớn hơn hoặc bằng 900, FALSE nếu không.

Ví dụ 2: Kết hợp hàm OR và hàm IF để kiểm tra

Kết hợp OR và hàm IF: Ta vẫn sử dụng số liệu ở ví dụ 1

Giải thích như sau:

Công thức trả về ” Kết quả tốt ” nếu một số trong ô B2 lớn hơn 1000 hoặc số trong C2 lớn hơn 900, ” Kết quả không tốt ” nếu không.

– THÀNH THẠO Kế toán thực tế trên phần mềm MISA chỉ sau 24 ngày – Học xong làm ngay

– Thành thạo Excel 2010 trong 3 giờ

– Kế toán thuế của doanh nghiệp

Hàm COUNTIF được dùng để làm gì?

Hãy tưởng tượng rằng bạn đang có một tệp tổng hợp các mục chưa được sắp xếp gồm: sản phẩm, doanh số bán hàng, số tháng và bạn đang muốn biết tần suất bán hàng của một sản phẩm cụ thể. Thay vì bạn sắp xếp chúng một cách thủ công, thì hãy sử dụng tính năng của hàm COUNTIF.

Hàm COUNTIF là gì?

Trong Microsoft Excel, COUNTIF là một hàm được dùng để thống kê số liệu. Lấy ví dụ, bằng cách tìm hiểu tần suất bán hàng của một danh sách sản phẩm, bạn sẽ dễ dàng nhận biết được mặt hàng nào đang có doanh số bán tốt nhất. Những dữ liệu thống kê này sau đó còn có thể được sử dụng tạo cách thành phần trực quan như sơ đồ và đồ thị.

Hàm COUNTIF trong Excel về cơ bản là sự kết hợp giữa hàm IF và hàm COUNT hoặc COUNTA (hàm thống kê). Sự kết hợp này để đảm bảo rằng Excel chỉ đếm những ô đáp ứng một số tiêu chí nhất định mà bạn đưa ra.

Chú ý: Bạn đừng nhầm lẫn giữa hàm COUNTIF và SUMIF. Một hàm sẽ trả về đếm số lượng của một giá trị cụ thể (COUNTIF), còn một hàm sẽ tính tổng các giá trị thỏa điều kiện bạn đặt ra (SUMIF).

Công thức hàm COUNTIF =COUNTIF (RANGE, CRITERIA)

Range: Phạm vi các ô được tính toán, thống kê.

Hàm COUNTIF – Cách sử dụng và ví dụ minh họa =COUNTIF(A3:A11,E3)

Range: A3:A11. Do bạn muốn đếm các giá trị có thể đáp ứng giá trị Criteria trong phạm vi những ô này.

Criteria : E3. Do bạn chỉ muốn đếm các giá trị trong phạm vi nêu trên nếu nó có giá trị trùng khớp với ô E3, chính là Toyota.

Và kết quả trả về sẽ là 4.

Thay vì sử dụng tham chiếu ô ( E3) làm tiêu chí lựa chọn, bạn cũng có thể thay thế nó bằng một giá trị văn bản -bằng cách gõ ” Toyota ” để có kết quả tương tự. Một lợi thế khác của việc sử dụng tham chiếu ô đó là bạn có thể đếm luôn cả các thương hiệu xe hơi khác nhau (nếu muốn). Lúc đó bạn chỉ cần kéo chuột xuống để copy công thức thay vì phải nhập thủ công tên các hãng xe khác nhau vào từng công thức.

Mẹo: Nếu như bạn nhìn thấy có khá nhiều ô chứa giá trị văn bản bị sai chính tả, thì bạn có thể nhanh chóng sửa lại chúng bằng cách dùng tính năng REPLACE. Bạn nhấn phím CTRL + H và một Menu sẽ được bật lên, sau đó bạn hãy nhập văn bản sai (văn bản bạn muốn thay đổi) và nhập giá trị văn bản mới ở trường bên dưới nó. Cuối cùng bạn nhấp vào REPLACE ALL.

Cách dùng hàm COUNTIF để đếm ô chứa văn bản trong Excel

Bạn có thể sử dụng hàm COUNTA để đếm cả giá trị số và giá trị văn bản, sau đó dùng hàm COUNT để đếm các giá trị số riêng lẻ và sau đó thực hiện phép tính trừ giữa các con số trả về của hàm COUNTA và hàm COUNT. Tuy nhiên, cách làm này khá rườm rà và làm mất thời gian của bạn, hôm nay tôi sẽ chỉ cho bạn cách dễ dàng hơn để đạt được kết quả tương tự.

Bạn hãy xem xét ví dụ minh họa bên dưới:

=COUNTIF(A3:A10,"*")

Range : A3:A10. Đây là phạm vi bạn muốn đếm các giá trị văn bản trong phạm vi.

Criteria : “*”. Có ý nghĩa rằng bạn chỉ muốn đếm các giá trị văn bản chèn một dấu HOA THỊ (*) được bọc trong dấu ngoặc kép.

Và kết quả trả về sẽ là 5.

Dấu HOA THỊ (*) được sử dụng làm ký tự đại diện để thể hiện cho một hay nhiều ký tự. Vì bạn chỉ muốn đếm tất cả các ô chứa giá trị văn bản nên việc chèn dấu HOA THỊ sẽ giúp bạn đạt được mục đích này.

Hàm SUMIFS cũng giống như các , hàm SUMIF mà mình đã hướng dẫn ở trong những bài trước đó là dùng để tính tổng trong Excel. Nhưng hàm SUMIFS nó cao siêu hơn 1 chút đó là nó là hàm tính tổng nhiều điều kiện. Trong bài viết này mình sẽ hướng dẫn các bạn cách dùng hàm SUMIFS trong Excel để tính tổng có nhiều điều kiện thông qua ví dụ cụ thể.

Hướng dẫn cách dùng hàm SUMIFS trong Excel Cú pháp hàm SUMIFS

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Các giá trị trong hàm SUMIFS

sum_range: Là tham chiếu đến các ô, vùng chọn cần tính tổng. Các ô được tính là ô chứa giá trị số, các ô trống, giá trị là chữ, văn bản sẽ được bỏ qua. Đây là giá trị bắt buộc.

criteria_range1: Là phạm vi cần tính được kiểm tra bằng điều kiện criteria1. Đây là giá trị bắt buộc.

criteria1: Đây là điều kiện áp dụng cho criteria_range1. Nó có thể là một số, biểu thức, tham chiếu ô để xác định ô nào trong criteria_range1 sẽ được tính tổng. Đây là giá trị bắt buộc.

[criteria_range2, criteria2],…: Đây là cặp phạm vi và điều kiện bổ sung nếu có. Hàm SUMIFS cho phép sử dụng tối đa 127 cặp [criteria_range, criteria]. Đây là giá trị tùy chọn.

Lưu ý khi sử dụng hàm SUMIFS

Các criteria_range phải có cùng kích thước vùng chọn giống với , tức là criteria_range và sum_range phải có số hàng và số cột bằng nhau.

Các ô trong sum_range có giá trị là TRUE sẽ được định nghĩa giá trị là 1 và ô có giá trị là FALSE sẽ được định nghĩa giá trị là 0 (không).

Điều kiện criteria có thể dùng các ký tự đại diện như sau: dấu hỏi chấm (?) thay cho ký tự đơn, dấu sao (*) thay cho một chuỗi. Nếu trong điều kiện muốn đặt là dấu hỏi chấm (?) hoặc dấu sao (*) thì hãy nhập thêm dấu ~ ở trước.

Các giá trị của điều kiện (criteria) là văn bản thì cần để trong dấu ngoặc kép ” “.

Để hiểu rõ hơn về cách dùng hàm SUMIFS trong Excel chúng ta sẽ đi vào làm ví dụ thực tế như bên dưới để hiểu rõ hơn về cách dùng hàm SUMIFS. Chúng ta có bảng Excel như sau:

Yêu cầu tính toán

1. Tính tổng số lượng sản phẩm của nhà cung cấp Adayroi với giá bán trên 6.000.000đ

2. Tính tổng số lượng sản phẩm của nhà cung cấp Lazada trừ sản phẩm Samsung S8

Đây là kết quả:

Đây là kết quả

Download bài tập ví dụ về hàm SUMIFS trong Excel

Lời kết

So với hàm SUM hay hàm SUMIF thì hàm SUMIFS nâng cao hơn và nó giúp bạn nhiều hơn trong việc tính tổng với nhiều điều kiện để bạn có thể giải các bài toán phức tạp hơn trong thực tế. Với những gì mình chia sẻ ở trên hi vọng sẽ giúp bạn có thể sử dụng hàm SUMIFS 1 cách nhuần nhuyễn hơn.

Trình duyệt của bạn không hỗ trợ nghe audio.

Hàm LEN là gì?

Trong tất cả các hàm Excel có lẽ hàm LEN được coi là hàm dễ và đơn giản nhất. Tên hàm cũng rất dễ nhớ, không có gì ngoài ba ký tự đầu của từ “length”. Và đó cũng chính là tác dụng thật sự của hàm LEN trả về chiều dài của chuỗi ký tự, hay chiều dài của một ô.

Nói cách khác, bạn có thể sử dụng hàm LEN trong Excel để đếm tất cả ký tự trong một ô, bao gồm chữ cái, chữ số, ký tự đặc biệt, và tất cả khoảng trống (trắng).

Tác dụng của hàm LEN trong Excel

Hàm LEN trong Excel đếm tất cả ký tự trong một ô, rồi trả về chiều dài chuỗi.

Cú pháp hàm LEN =LEN(text)

Text chính là chuỗi ký tự nơi mà bạn muốn đếm số ký tự hoặc ô trong Excel

Bạn sẽ tìm thấy một vài công thức đơn giản bên dưới nhằm giúp bạn hiểu sơ qua công dụng của hàm LEN trong Excel.

=LEN(135) – trả về 3, bởi vì 3 chữ số được nhập vào câu lệnh text.

=LEN(“NAMXUYEN”) – trả về 8 bởi vì từ NAMXUYEN có 8 chữ cái. Giống như bất cứ công thức Excel nào khác, hàm LEN bắt buộc bạn phải đặt chuỗi ký tự bên trong dấu ngoặc kép, và đương nhiên rồi dấu ngoặc kép này sẽ không được đếm.

Khi sử dụng công thức LEN, bạn có thể cần phải nhập tham chiếu ô thay vì chữ số hay chuỗi ký tự , để đếm số ký tự trong một ô cụ thể hay một dải ô.

Ví dụ

Để tính được chiều dài chuỗi ký tự trong ô A5, bạn sẽ dùng công thức này:

=LEN(A5)

Cách đếm tất cả các ký tự trong một ô ( bao gồm cả khoảng trắng)

Như đã nói lúc trước, hàm LEN trong Excel đếm tất cả ký tự trong một ô cụ thể một cách tuyệt đối, bao gồm tất cả khoảng trống hay thụt đầu dòng, thụt cuối dòng, và cả khoảng trống giữa các từ.

Ví dụ, để tính chiều dài của ô A3, bạn sử dụng công thức này:

=LEN(A3)

Đếm số ký tự trong nhiều ô

Để đếm số ký tự trong nhiều ô, hãy chọn ô có công thức LEN rồi sao chép sang ô khác, ví dụ bằng cách kéo ô vuông nằm dưới góc bên phải .

Ngay khi sao chép công thức, hàm LEN sẽ trả về số ký tự đếm được trong mỗi ô.

Và một lần nữa, hãy lưu ý rằng hàm LEN đếm tất cả mọi thứ một cách tuyệt đối bao gồm chữ cái, chữ số, khoảng trống, dấu phẩy, dấu ngoặc kép, dấu ngoặc đơn, và còn nhiều nữa:

Lưu ý. Khi sao chép một công thức xuống các cột khác, hãy chắc rằng bạn sử dụng tham chiếu ô tương đối chẳng hạn như LEN(A3), hay một tham chiếu hỗn hợp như LEN($A3) chỉ dùng cố định cho cột, nên công thức LEN của bạn sẽ điều chỉnh một cách thích hợp với vị trí mới.

Đếm tất cả ký tự trong nhiều ô

Cách rõ ràng nhất để đếm tất cả ký tự trong vài ô là sử dụng nhiều hàm LEN.

Ví dụ

Hoặc có thể sử dụng hàm SUM để tính tổng số ký tự được trả về bởi hàm LEN:

=LEN(A2)+LEN(A3)+LEN(A4)

Cách nào cũng được, công thức đều đếm số ký tự trong mỗi ô xác định rồi trả về tổng chiều dài chuỗi:

=SUM(LEN(A2),LEN(A3),LEN(A4))

Cách đếm số ký tự không tính khoảng trống dầu dòng và cuối dòng

Khi thao tác với bảng tính lớn thì vấn đề phổ biến chính là khoảng cách đầu dòng và cuối dòng, ví dụ: khoảng trống ở đầu hay ở cuối ký tự. Thong thường chúng ta hầu như không thấy chúng trên bảng tính, nhưng sau khi bạn gặp chúng một vài lần, thì bạn sẽ dễ nhận thấy chúng hơn.

Nếu bạn nghi ngờ có vài khoảng trống vô hình trong các ô thì hàm LEN trong Excel sẽ giúp ích rất nhiều. Hy vọng bạn còn nhớ, nó đếm tất cả khoảng trống trong phép đếm ký tự:

Để tính được chiều dài chuỗi mà không đếm khoảng trống đầu dòng và cuối dòng, hãy nhớ lồng ghép hàm TRIM vào hàm LEN trong Excel:

Cách đếm số ký tự trong một ô không tính các khoảng trống

=LEN(TRIM(A5))

Nếu mục đích của bạn chỉ là đếm số ký tự mà không tính các khoảng trống cho dù đó là khoảng trống đầu dòng, cuối dòng hay giữa các từ, thì bạn sẽ cần một công thức phức tạp hơn:

Chúng ta sử dụng hàm SUBSTITUTE thay thế ký tự này với ký tự khác. Trong công thức trên, bạn thay thế khoảng trống (” “) bằng một ký tự rỗng, ví dụ một chuỗi ký tự rỗng (“”). Và bởi vì bạn lồng ghép hàm SUBSTITUTE vào hàm LEN nên phép thế thật sự không được thực hiện trong các ô, mà nó chỉ hướng dẫn công thức LEN tính chiều dài chuỗi mà không tính các khoảng trống.

=LEN(SUBSTITUTE(A5,” “,””))

Cách đếm số ký tự trước hay sau một ký tự xác định

Thông thường, bạn cần phải biết chiều dài của một phần nhất định của chuỗi ký tự, thay vì đếm tổng số ký tự trong một ô.

Giả sử, bạn có một danh sách mã hàng hóa như sau:

Các mã hàng được ngăn cách bởi dấu “-” và tất cả mã hàng hóa hợp lệ đều có chính xác 5 ký tự trong nhóm đầu tiên. Vậy làm thế nào để chúng ta chỉ ra được mã hàng hóa nào là không hợp lệ? Cách giải quyết đơn giản là đếm số ký tự trong nhóm đầu tiên

Công thức áp dụng cho bài toán trên có dạng như sau:

Và bây giờ, hãy chia nhỏ công thức ra để bạn có thể hiểu quy luật của nó:

=LEN(LEFT($A2, SEARCH(“-“, $A2) -1))

Bạn sử dụng hàm SEARCH để trả về vị trí của dấu gạch ngang đầu tiên (“-“) trong ô A2:

Sau đó, bạn sử dụng hàm LEFT để trả về các ký tự phía bên trái của chuỗi ký tự, rồi lấy kết quả trừ 1 bởi vì bạn không muốn tính luôn dấu gạch ngang:

SEARCH(“-“, $A2)

LEFT($A2, SEARCH(“-“, $A2) -1)

Và cuối cùng, bạn có hàm LEN để trả về chiều dài của chuỗi đó.

Hay, bạn có thể xác định mã hàng hóa không hợp lệ bằng cách lồng ghép công thức LEN ở trên vào hàm IF:

Ví dụ, trong một danh sách tên, có thể bạn muốn biết họ của một người có bao nhiêu ký tự. Công thức LEN sau đây sẽ giải quyết việc này:

=LEN(RIGHT(A2, LEN(A2) -SEARCH(” “,A2)))

Công thức hoạt động như thế nào:

Xin hãy lưu ý rằng, để công thức hoạt động một cách chính xác, mỗi ô nên có chỉ một khoảng trống, ví dụ chỉ có tên và họ, không có tên lót, danh hiệu hay hậu tố.

Đầu tiên, bạn xác định vị trí của khoảng trống ” ” trong chuỗi ký tự bằng cách sử dụng hàm SEARCH: SEARCH(” “,A2)))

Sau đó, bạn đếm có bao nhiêu ký tự phía sau khoảng trống. Về việc này, bạn lấy tổng chiều dài chuỗi trừ đi vị trí khoảng trống:

LEN(A2) -SEARCH(” “, A2)))

Sau đó, bạn sử dụng hàm RIGHT để trả về tất cả ký tự phía sau khoảng trống.

Và cuối cùng, bạn sử dụng hàm LEN để tính chiều dài chuỗi được trả về bởi hàm RIGHT.