Hướng dẫn áp dụng công thức mảng của Excel lên GoogleSheet

 Trong nhiều tình huống thực tế, người dùng sử dụng song song cả Excel và Googlesheet. Khi đó thường xảy ra tình huống làm công thức trên Excel chạy bình thường nhưng khi upload file hoặc copy công thức lên GoogleSheet thì công thức bị lỗi, và lỗi chủ yếu xuất hiện với công thức mảng.

NHẬN DẠNG CÔNG THỨC MẢNG

Một số đặc điểm nhận dạng công thức mảng

- Công thức có xuất hiện dấu { } trước và sau công thức. Ví dụ: {=SUM(IF(A1:A10="N",C1:C10,0))}

- Có xuất hiện toán tử so sánh cả 1 cột hoặc 1 vùng dữ liệu với 1 giá trị. Ví dụ: A1:A10="N"

- So sánh hoặc phép tính với các vùng. Ví dụ: C1:C10*D1:D10

Tham khảo thêm về nhận dạng công thức mảng TẠI ĐÂY

HƯỚNG DẪN ĐƯA CÔNG THỨC MẢNG CỦA EXCEL LÊN GOOGLE TRANG TÍNH

Bước 1: Kiểm tra công thức sử dụng dấu ngăn cách là dấu , hay ; thì sửa dấu tương ứng cho đúng dấu ngăn cách giữa các tham số trên Googlesheet

Bước 2: Lồng thêm hàm ARRAYFORMULA bên ngoài công thức 

Ví dụ: Công thức trên Excel là: =SUM(IF(A1:A10="N",C1:C10,0))

Thì khi chuyển sang Googlesheet sẽ sửa thành: =ARRAYFORMULA(SUM(IF(A1:A10="N",C1:C10,0)))

Ví dụ như hình ảnh sử dụng hàm LOOKUP với mảng và khi đưa lên Googlesheet thì thêm hàm ARRAYFORMULA bên ngoài

Lưu ý: Trong Excel có hàm AGGREGATE cho phép sử dụng công thức mảng ở 1 vài tham số. Nhưng trong Googlesheet thì chưa có hàm này. Người dùng sẽ thay thế bằng hàm FILTER, LARGE, SMALL theo tình huống cụ thể

Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp

{Đt Zalo} - 038 696 1334

Xem chi tiết

Hướng dẫn khắc phục lỗi hàm IMPORTRANGE không nhận quyền truy cập

 IMPORTRANGE là hàm của Google trang tính (Googlesheet) để hỗ trợ người dùng làm công thức liên kết giữa các file trong Google Drive. Tuy nhiên, người dùng thường xuyên gặp phải lỗi "bạn không có quyền truy cập trang tính đó"

Nguyên nhân và cách khắc phục: Do 1 trong 2 nguyên nhân sau

Nguyên nhân 1: CHƯA THỰC HIỆN CHIA SẺ FILE TRÊN GOOGLE TRANG TÍNH

Các bước thực hiện chia sẻ như sau:

Bước 1: Vào Tệp => Chọn Chia sẻ => Chia sẻ với người khác

Bước 2: Màn hình thiết lập chia sẻ hiển thị => Thêm email vào mục Thêm người vào nhóm và chọn Quyền truy cập chung

Bước 3: Chạy lại hàm IMPORTRANGE

Nguyên nhân 2:  DO FILE CHƯA Ở DẠNG GOOGLE TRANG TÍNH CHUẨN

Nguyên nhân này là do file được upload từ máy tính lên google drive nên file chưa ở dạng trang tính chuẩn

Cách khắc phục: Theo hướng dẫn TẠI ĐÂY

Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp

{Đt Zalo} - 038 696 1334

Xem chi tiết

Một số tính năng của Googlesheet bị ẩn và cách khắc phục

 Trong nhiều tình huống khi người dùng sử dụng google trang tính thì xảy ra tình trạng một số tính năng không xuất hiện ở file này nhưng lại có ở file khác như: Protect sheet and ranges (bảo vệ trang tính và dải ô),Tiện ích mở rộng, Macro...

Nguyên nhân: File không xuất hiện những tính năng này là những file được upload từ máy tính lên Google drive và file này chưa hoàn toàn ở dạng google trang tính - googlesheet

Cách khắc phục: Chuyển file về định dạng google trang tính chuẩn

CÁCH CHUYỂN FILE VỀ DẠNG GOOGLE TRANG TÍNH

Bước 1: Mở file trên Google sheet rồi vào Tệp (file) => chọn Lưu dưới dạng Google trang tính

Bước 2: Cửa số mới sẽ được hiện ra, tức file này đã được nhân bản tạo thành file mới và file mới đã được lưu ở dạng google trang tính => Khi đó người dùng sẽ sử dụng các tính năng bị ẩn bình thường.

Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp

{Đt Zalo} - 038 696 1334

Xem chi tiết

Hướng dẫn sử dụng hàm IMPORTRANGE trong Googlesheet và kết hợp cùng QUERY

 IMPORTRANGE là hàm dùng để lấy dữ liệu từ file Google trang tính khác thông qua đường link Url.

Cấu trúc hàm IMPORTRANGE:

=IMPORTRANGE("spreadsheet_url"; "range_string")

Trong đó:

- "spreadsheet_url" là đường dẫn Url của 1 file google trang tính khác ở dạng text. Ví dụ: "https://docs.google.com/spreadsheets/d/1H2ItWWRmsm4feEe_91s8o4ocvlpTd7CZ7YhTh0Yd-Rk"

- "range_string" là vùng dữ liệu cần lấy từ trang tính đó và địa chỉ này ở dạng text. Ví dụ: "DATA!A1:F21" => Tức lấy dữ liệu từ A1:F21 trong sheet DATA

Ví dụ mẫu:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1H2ItWWRmsm4feEe_91s8o4ocvlpTd7CZ7YhTh0Yd-Rk";"DATA!A1:E21")

Do hàm IMPORTRANGE chỉ thực hiện lấy vùng dữ liệu từ 1 trang tính khác, khi đó sẽ dụng hàm IMPORTRANGE và QUERY kết hợp với nhau để làm các báo cáo truy vấn

Cấu trúc kết hợp hàm IMPORTRANGE và QUERY

=QUERY(IMPORTRANGE("spreadsheet_url"; "range_string");"sql_string")

Trong đó:

- IMPORTRANGE("spreadsheet_url"; "range_string") trả về kết quả là 1 mảng

- "sql_string" là chuỗi câu lệnh SQL. Phần chuỗi câu lệnh SQL sẽ được sử dụng theo cấu trúc Coln. Với Col viết tắt bởi từ Column, n là thứ mấy. Ví dụ: Col2 => cột số 2

Ví dụ mẫu: Lấy ra các cột 1,2,3,4 với điều kiện, cột 2 là HH1 thì công thức là

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1H2ItWWRmsm4feEe_91s8o4ocvlpTd7CZ7YhTh0Yd-Rk";"DATA!A1:E21");"Select Col1, Col2, Col3, Col4 Where Col2='HH1'")

Trường hợp khi sử dụng hàm Query kết hợp với hàm Importrange mà hiển thị lỗi không có quyền truy cập thì khắc phục theo hướng dẫn TẠI ĐÂY

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp

{Đt+Zalo} - 038 696 1334

Xem chi tiết

Hướng dẫn dùng hàm SPLIT trong Googlesheet

Hàm SPLIT là hàm tách chuỗi trong Googlsheet với kết quả trả về là một mảng các phần tử

Ví dụ tình huống: Chuỗi Text ngăn cách nhau bởi dấu - và cần tách ra sau mỗi dấu - là một ô độc lập (như hình ảnh)

Công thức mẫu: =SPLIT(A1;"-")

Khi dùng thực tế thì chỉ cần thay địa chỉ ô và ký tự ngăn cách. Ví dụ có thể là các ký tự như: dấu phẩy (,), dấu chấm phẩy (;), 

Ứng dụng: Đây là hàm có tính ứng dụng cao trong xử lý tách chuỗi dữ liệu, nó tương đương như tính năng Text to columns của Excel thông thường. Đồng thời đây là hàm trả về mảng nên thuận tiện hơn cho người dùng nếu kết hợp với các hàm khác.

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp

{Đt+Zalo} - 038 696 1334

Xem chi tiết

Hướng dẫn sử dụng hàm QUERY trên Googlesheet

 QUERY là 1 hàm rất hay của googlesheet thông qua lệnh SQL để lấy ra mảng dữ liệu. Cấu trúc lệnh SQL trong hàm QUERY được tối giản nên nhìn hơi khác so với lệnh SQL chuẩn nhưng về bản chất là tương đương

Để thuận tiện cho việc hướng dẫn dùng hàm QUERY thì các ví dụ thực hành dưới dây sẽ theo tình huống giả lập như sau:

- Bảng số liệu (như hình ảnh): Đặt từ cột A đến cột E. Vùng có số liệu là A1:E21

- Tên sheet được đặt là DATA

Cấu trúc hàm QUERY:

=QUERY(range,"sql_statement")

- Range là vùng dữ liệu tham chiếu tới

- "sql_statement" là chuỗi lệnh SQL để ra báo cáo truy vấn

CÁC VÍ DỤ THỰC HÀNH

Ví dụ 1: Lấy tất cả từ bảng dữ liệu thỏa mãn 1 điều kiện là Mã hàng là HH4

Công thức: =QUERY(DATA!A1:E21,"Select * Where B='HH4'")

Trong đó:

- DATA!A1:E21 là vùng dữ liệu nguồn

- "Select * Where B='HH4'" là chuỗi text lệnh SQL nên phải đặt trong dấu nháy kép "..."

- Select và Where là 2 từ khóa bắt buộc: SELECT lấy ra gì (* tức là lấy ra tất cả), WHERE điều kiện là gì (cột B là HH4. Nhưng HH4 lại là text thì trong SQL chuỗi HH4 phải đặt trong dấu nháy đơn '...')

Ví dụ 2: Lấy cột A, B, C, D thỏa mãn 2 điều kiện: Khách hàng là KH1 và Số lượng >10

Công thức: =QUERY(DATA!A1:E21;"SELECT A, B, C, D WHERE E='KH1' and C>10")

Trong đó:

- SELCT A, B, C, D lấy ra những cột cụ thể

- "and" là từ khóa để yêu cầu thực hiện lọc đồng thời các điều kiện của WHERE.

Ví dụ 3: Lấy tất cả các cột A, B, C, D thỏa mãn các điều kiện: Khách hàng là KH1, từ ngày 01/01/2021 đến 31/01/2021

Công thức: =QUERY(DATA!A1:E21;"SELECT A, B, C, D WHERE E='KH1' and A >= date '2021-01-01' and A <= date '2021-01-31'")

Trong đó: Ngày tháng trong lệnh SQL của googlesheet sẽ viết là "date 'yyyy-mm-dd'"

Ví dụ 4: Tính tổng số lượng của từng mã hàng của KH1

Công thức: =QUERY(DATA!A1:E21;"SELECT B,Sum(C) WHERE E='KH1' GROUP BY B")

Trong đó: GROUP BY là từ khóa bắt buộc để tổng hợp các cột không sử dụng hàm thống kê

Ví dụ 5: Tính tổng số lượng từng mã hàng và sắp xếp số lượng giảm dần

Công thức: =QUERY(DATA!A1:E21;"SELECT B,Sum(C) GROUP BY B ORDER BY SUM(C) DESC")

Trong đó:

- ORDER BY là từ khóa dùng để sắp xếp

- DESC là từ khóa để yêu cầu sắp xếp giảm dần, nếu dùng ASC thì sẽ là sắp xếp tăng dần

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp

{Đt+Zalo} - 038 696 1334

Xem chi tiết

Hướng dẫn sử dụng hàm GOOGLETRANSLATE trên Googlesheet

Googlesheet có hàm GOOGLETRANSLATE để dịch từ ngôn ngữ này sang ngôn ngữ khác bằng công thức ngay trên bảng tính.

Cấu trúc hàm GOOGLETRANSLATE:

=GOOGLETRANSLATE(text, “source_language”, “target _language”)

Trong đó:

- Text là văn bản cần dịch (ô có chứa văn bản)

- source_language là ký hiệu viết tắt của ngôn ngữ của text

- target _language là ký hiệu viết tắt của ngôn ngữ cần dịch sang (kết qua dịch)

Cách dùng hàm GOOGLETRANSLATE

Để dùng được công thức này thì người dùng cần biết được ký hiệu viết tắt của ngôn ngữ quy định trong Google. Người dùng có thể tham khảo một số ký hiệu sau

"en" - Tiếng Anh

"zh" - Tiếng Trung

"vi" - Tiếng Việt

"ja" - Tiếng Nhật

"ko" - Tiếng Hàn

"fr" - Tiếng Pháp

Ví dụ: Dịch từ Tiếng Anh sang Tiếng Việt

=GOOGLETRANSLATE(A1;"en","vi")

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp

{Đt+Zalo} - 038 696 1334

Xem chi tiết