Nếu bạn làm việc với Google Sheets đủ thường xuyên, chắc chắn bạn sẽ cần phải lấy dữ liệu từ bảng tính này sang bảng tính khác. Show
Hàm IMPORTRANGE trong Google Sheets sẽ giúp chúng ta liên kết dữ liệu từ những bảng tính khác nhau, để tìm kiếm dữ liệu nhanh chóng, lấy dữ liệu theo những yêu cầu hiển thị trong hàm. Như vậy bạn có thể dùng hàm IMPORTRANGE để trích xuất dữ liệu, liên kết giữ liệu nhanh chóng trong các bảng dữ liệu Google Sheets. Các sử dụng hàm IMPORTRANGE trong Google Sheets cũng đơn giản và có thể kết hợp thêm với một số hàm trong Google Sheets. Bài viết dưới đây sẽ hướng dẫn bạn dùng hàm IMPORTRANGE trong Google Sheets. Cấu trúc hàm IMPORTRANGE trong Google SheetsCấu trúc hàm IMPORTRANGE Google Sheets =IMPORTRANGE(“spreadsheet_url”; “chuỗi_dải_ô”) Trong đó:
Chúng ta sẽ lấy ví dụ với bảng dữ liệu Điểm chuẩn lớp 10-2019, với yêu cầu trích xuất dữ liệu từ ô B2 đến ô C29 trong bảng tính Page 1.  Bạn mở 1 bảng tính mới hoàn toàn trong Google Sheets để lấy dữ liệu của bảng tính mình cần. Đầu tiên bạn cần sao chép link của tài liệu Điểm chuẩn lớp 10-2019. Tiếp đến tại giao diện bảng tính mới, chúng ta nhập công thức hàm như dưới đây rồi nhấn Enter. =IMPORTRANGE("https://docs.google.com/spreadsheets/d/19AGre5-Fvin6Z4D0bW6e4iq6yYX13kar0EIvXAVh-VQ/edit#gid=887314864"; "Page1!B2:C29")Ngay lập tức bạn sẽ thấy báo #REF!. Bạn cần nhấn vào thông báo này và nhấn Cho phép truy cập để cho phép hàm IMPORTRANGE truy cập dữ liệu từ trang tính khác. Kết quả dữ liệu từ trang tính bạn chọn đã hiển thị ở trang tính khác như hình dưới đây. Cách sử dụng hàm IMPORTRANGE trong Google Sheets1. Chỉ với hai đối số, việc sử dụng hàm IMPORTRANGE thường khá đơn giản. Giả sử bạn có một bảng tính và bạn muốn nhập bảng vào một bảng tính mới. 2. Nhấp vào URL trong thanh địa chỉ ở đầu trình duyệt và sao chép nó. Ngoài ra, bạn có thể chỉ sao chép key bảng tính từ trong URL. 3. Trong bảng tính mới, nhập "=IMPORTRANGE(" - không có dấu ngoặc kép. 4. Dán URL và thêm dấu ngoặc kép ("). 5. Nhập dấu phẩy, thêm dấu ngoặc kép (") và nhập phạm vi ô bạn muốn đưa vào. Nó sẽ giống như sau: "Sheet1!B1:C6". Ở đây chỉ định rằng ví dụ muốn bảng tính có tên "Sheet1" và muốn các ô từ B1 đến C6. 6. Thêm dấu ngoặc đóng và nhấn Enter. 7. Hàm hoàn chỉnh sẽ trông giống như sau: =IMPORTRANGE("https://docs.google.com/spreadsheets/d/1Zoq0M0RG-RLYZ9HjOf01ff9eSPIYY3s/edit#gid=1027643093", "Sheet2!A1:C12")8. Tuy nhiên, bạn có thể nhận thấy rằng dữ liệu không được nhập - có lỗi #REF! trong ô thay thế. Nhấp vào ô này và bạn sẽ thấy thông báo rằng bạn cần kết nối các trang tính. Nhấp vào "Allow access" và sau đó, dữ liệu sẽ xuất hiện. Bạn sẽ chỉ cần thực hiện việc này một lần cho mỗi bảng tính mà bạn nhập dữ liệu từ đó. Cách dùng hàm IMPORTRANGE với hàm QUERYKhi kết hợp hàm IMPORTRANGE với hàm QUERY thì bạn có thể lấy dữ liệu chính xác theo điều kiện cần trích xuất. Chúng ta sẽ khoanh vùng dữ liệu từ từ ô B2 đến ô C29 trong bảng tính Page 1 và chỉ lấy cột NV1. Bạn nhập công thức như dưới đây rồi nhấn Enter. =QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/19AGre5-Fvin6Z4D0bW6e4iq6yYX13kar0EIvXAVh-VQ/edit#gid=576960759";"Page1!B2:C29");"SELECT Col2")Kết quả sẽ chỉ hiển thị cột điểm NV1. Hoặc bạn cũng có thể lấy giá trị điểm của những trường có điểm NV1<40 chẳng hạn. Chúng ta nhập công thức hàm như bên dưới rồi nhấn Enter. =QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/19AGre5-Fvin6Z4D0bW6e4iq6yYX13kar0EIvXAVh-VQ/edit#gid=576960759";"Page1!B2:C29");"Sect Col1 where Col2 <40")Kết quả sẽ hiển thị tên các trường có điểm NV1 thấp hơn 40 điểm. Xem thêm:
Google Sheets là công cụ vô cùng linh hoạt và vượt trội kết hợp giữa thiết lập và tính toán dữ liệu dưới dạng bảng tính. Nó hoạt động dựa trên tính năng đám mây, vì vậy nó cung cấp nhiều chức năng tương tác, thu thập dữ liệu tự động, thậm chí kéo dữ liệu từ APIs bên thứ ba. Bạn thường làm việc với các bảng tính như Excel, iWork Numbers, Zoho Sheet hoặc Open Office Calc hẳn sẽ dễ dàng sử dụng Google Sheets. Còn nếu bạn mới bắt đầu làm quen với Sheets, hoặc đang tìm hiểu chương trình Spreadsheet thì dưới đây là sáu hàm hữu ích cho dữ liệu của bạn. Để biết thêm các mẹo khác trong Google SpreadSheets hãy ấn vào đây. 1.ARRAYFORMULAHàm này giúp bạn giảm bớt thời gian bỏ vào việc chỉnh sửa các công thức trong bảng tính của mình. Khi cần áp dụng các hàm cho hàng nghìn hàng và cột trong trang tính, bạn nên sử dụng ARRAYFORMULA thay thế cho các hàm không theo mảng (non-array). Các hàm không theo mảng là những hàm mà bạn tạo trong một ô và sau đó sao chép-dán chúng vào một ô khác trong trang tính. Chương trình Google Sheets đủ thông minh để sửa đổi công thức theo địa chỉ ô. Tuy nhiên, làm như vậy, bạn sẽ mở tạo ra những vấn đề sau:
Giả sử rằng bạn chịu trách nhiệm tạo một tài liệu bảng tính có tên học sinh, điểm theo môn học và tổng điểm của học sinh. Bây giờ, nếu bạn không chỉ phải tính điểm cho một vài học sinh trong lớp, mà là tất cả học sinh trên toàn thành phố, công thức dựa trên toán tử `+` thông thường sẽ tốn thời gian. Bạn có thể sử dụng ARRAYFORMULA như được đề cập bên dưới. =ArrayFormula(B2:B+C2:C+D2:D+E2:E+F2:F)Trong công thức này, phạm vi B2:B chỉ định vô hạn. Bạn có thể làm cho nó hữu hạn bằng cách chỉnh sửa phạm vi thành B2:B10, v.v... 2. VLOOKUP và HLOOKUPVới ví dụ sau sẽ chứng minh được các hàm tìm kiếm của Google Sheets là tốt nhất. Chúng cho phép tìm kiếm để chuỗi của một từ và sau đó đọc giá trị sang cột hoặc dòng tương đương. Điều này thực sự hữu ích nếu bạn có các bộ dữ liệu khác nhau có cùng đối tượng trong bảng tính. Ví dụ, các dòng của dữ liệu về sản phẩm, nhân sự hoặc dự án. Ở ví dụ dưới đây, hãy tưởng tượng bạn muốn theo dõi sự thay đổi số lượng táo, cam, lê của tháng Giêng và tháng Hai. Thứ tự của những trái cây có sẵn thay đổi theo từng tháng vì vậy không được sử dụng phép tính trừ cho các ô. Thay vào đó, hàm VLOOKUP tìm kiếm bảng dữ liệu theo hàng dọc cho đến khi nó tìm ra điểm chung của từ và sau đó đọc theo hàng ngang để tìm giá trị tương ứng trong cột liền kề. VLOOKUP viết tắt của Vertical Lookup bởi vì nó tìm từ theo hàng dọc và sau đó theo hàng ngang để cho ra giá trị trong khi Hlook up viết tắt của Horizontal Lookup bởi nó tìm từ theo hàng ngang và sau đó cho giá trị theo hàng dọc. Đầu tiên, với VLOOKUP sử dụng bảng và phép tính sau: =VLOOKUP(F2,$A$2:$B$6,2,false)Trong đó, "F2" giá trị cần tìm của "Apple" ở ô F2. Vì sử dụng VLOOKUP cho nên "$A$2:$B$6" của phép tính để chỉ cho Google Sheet tìm dọc theo bảng dữ liệu của tháng Giêng. "2" chỉ ra tìm "Apple" ở cột thứ 2. "False" là giả định nếu không tìm thấy giá trị tương đương, thì chúng bỏ qua sẽ tìm ở vị trí khác. Cuối cùng, hoàn thành phép tính tìm kiếm Apple trong bảng dữ liệu tháng Giêng và truy tìm giá trị 1003 trong cột thứ 2. Để tính sự thay đổi, chúng ta cần lấy hàm tìm kiếm của tháng Hai trừ đi hàm tìm kiếm của tháng một do đó, có phép tính như sau: =VLOOKUP(F2,$C$2:$D$6,2,false)-VLOOKUP(F2,$A$2:$B$6,2,false)Phép tính tìm kiếm thứ 2 tương tự như phép tính ở phía trên, nhưng bây giờ chúng ta nhập dữ liệu tháng Hai và lấy chúng trừ dữ liệu tháng Giêng. Vì vậy, viết phép tính tìm kiếm của dữ liệu tháng Hai tương tự như của tháng Giêng chỉ thay "$A$2:$B$6" cho "$C$2:$D$6". Bây giờ chúng sẽ lấy số lượng táo của tháng hai (785) trừ đi số lượng của tháng Giêng (785) và truy xuất ra giá trị -218. Tương tự như vậy, HLOOKUP biểu diễn hàm tương đương nhưng đọc theo hàng ngang và suy tìm theo hàng dọc. 3.IMPORTRANGEIMPORTRANGE là một hàm hữu ích nếu bạn cần lấy dữ liệu từ các trang tính khác nhau của Google Sheets. Thay vì sao chép dữ liệu từ một trang tính và dán vào trang tính mong muốn khác, bạn có thể sử dụng công thức này để tiết kiệm thời gian. Nếu ai đó không phải là bạn sở hữu trang tính, bạn phải có quyền truy cập vào trang tính trước khi có thể bắt đầu sử dụng hàm IMPORTRANGE. Trong quá trình kéo dữ liệu từ các trang tính khác nhau, bất kỳ thay đổi dữ liệu nào trên trang nguồn sẽ tự động phản ánh trên trang đích. Do đó, đây là công thức cần thiết nếu bạn đang lập báo cáo hoặc trang tổng quan bằng cách tìm nguồn dữ liệu từ các thành viên khác nhau trong nhóm dự án của mình. Trong video trên, bạn có thể thấy cách dễ dàng nhập dữ liệu nhân khẩu học Hoa Kỳ từ trang tính này sang trang tính khác. Bạn chỉ cần đề cập đến URL của trang tính, tên trang tính và phạm vi dữ liệu mà bạn muốn nhập. Công thức này không nhập bất kỳ định dạng trực quan nào từ nguồn. Bạn có thể sử dụng cú pháp công thức sau nếu muốn dùng thử: =IMPORTRANGE("URL","Sheet1!B3:R11")4. IMPORT: Chèn dữ liệu vào Google SheetsMột trong những tính năng vượt bậc của Google Sheets là hàm nhập. Hàm này cho phép kéo dữ liệu từ các nguồn khác nhau như XML, HTML, RSS - CSV vào bảng tính của bạn phù hợp để nhập cách danh sách các bài đăng trên blog, hàng tồn kho hoặc dữ liệu khác từ các dịch vụ khác. Ví dụ, bạn có thể nhập hàng loạt link từ URL như sau: =IMPORTXML("https://en.wikipedia.org/wiki/Moon_landing", "//a/@href")Nhập nội dung danh sách hoặc bảng từ URL xác định sử dụng: =IMPORTHTML("http://en.wikipedia.org/wiki/Demographics_of_India", "table", 4)Nhập RSS hoặc Atom: =IMPORTFEED("http://news.google.com/?output=atom")Nhập các nội dung của tâp tin CSV: =IMPORTDATA("http://www.census.gov/2010census/csv/pop_change.csv")Sự pha trộn giữa văn bản và các tính năng tự động của Google Sheets, hàm nhập dữ liệu đã trở thành công cụ mạnh để sàng lọc và thiết lập dữ liệu từ nguồn công cộng. 5.LENHàm LEN giúp bạn truy xuất độ dài của một chuỗi dữ liệu hoặc văn bản dưới dạng ký tự. Sẽ rất hữu ích khi bạn muốn tìm ra số lượng ký tự trong một văn bản mà không cần sử dụng bất kỳ công cụ nào của bên thứ ba. Nói một cách đơn giản, hãy sử dụng hàm LEN và đặt văn bản trong một dấu ngoặc đơn. Giả sử khách hàng cần bạn viết các phần Titles, Meta Titles và Meta Descriptions cho những blog họ cần. Họ cũng muốn bạn giới hạn độ dài ký tự cho các mục này ở một con số cụ thể. Bạn có thể sử dụng hàm LEN để theo dõi các mục này và độ dài ký tự của chúng. Nếu là nhà phát triển trang web hoặc ứng dụng, bạn cũng sẽ thấy hàm LEN hữu ích trong việc quản lý nội dung trong file Google Sheets. Hãy thử sử dụng công thức sau đây. =LEN(B2) =LEN("Example Text")6.IFERRORBảng tính của bạn có thể bị lộn xộn nếu có quá nhiều lỗi. Lỗi thường xảy ra khi bạn áp dụng nhiều công thức trên các cột và trang tính, nhưng không có nhiều dữ liệu để trả về bất kỳ giá trị nào. Nếu bạn chia sẻ những file như vậy với các thành viên trong nhóm hoặc khách hàng, họ có thể không thấy hài lòng. Hơn nữa, bạn cũng sẽ khó tránh khỏi việc mắc lỗi khi hoàn thành công việc. Hàm IFERROR sẽ giải cứu cho bạn. Đặt công thức của bạn bên trong hàm IFERROR và đề cập đến văn bản nào sẽ hiển thị nếu có lỗi. Video trên cho thấy việc sử dụng IFERROR trong các tình huống khi bạn quản lý bảng tính về giá sản phẩm hoặc phân loại học sinh. Dưới đây là các cú pháp công thức có thể hữu ích nếu bạn muốn tự mình thử: =iferror(D4/C4, 0) =iferror(VLOOKUP(A23,$A$13:$G$18,7,false),"ID Mismatch")7. COUNTIF và SUMIF trong Google SheetsSUMIF và COUNTIF dễ hơn hàm tìm kiếm một chút. Nếu mệnh đề logic trong CountIF hoặc SumIF là đúng, Google Sheets có thể đếm số lượng các trường hợp hoặc tổng hợp giá trị tương đương. Xem ví dụ dưới đây, bạn có thể đếm số lượng táo được bán với phép tính như sau: =COUNTIF(B2:B10,"Apple")Nó thể hiện rằng: Đếm số lượng các trường có từ Apple trong từ ô B2 đến B10. Bạn có thể tính tổng trọng lượng táo đã bán sử dụng hàm SumIF. =SUMIF(B2:B10,"Apple",C2:C10)Nó tìm kiếm số từ Apple được nhập trong cột B sau đó tính các giá trị tổng của ô tương đương trong cột C. 8. JOIN: Kết hợp từ trong Google SheetsHàm JOIN dùng để sâu chuỗi các giá trị thành một văn bản giúp thuận tiện cho việc sử dụng. Hoặc đơn giản là tổng hợp một vài giá trị chủ chốt hoặc một vài HTML. Gõ & để kết nối các giá trị của các ô khác nhau và sử dụng dấu ngoặc kép với bất cứ văn bản nào bạn muốn chèn. Ví dụ, chúng ta sử dụng phép tính sau: =A1 & " " & B1 & " and " & A2 & " " & B2và kết quả là "4 Apples and 5 Pears" Sử dụng hàm JOIN là thích hợp nhất khi liên kết nhiều giá trị. Bạn chỉ cần chỉ ra ký tự bạn muốn thêm vào giữa các giá trị và các giá trị ô bạn muốn. Ví dụ: =JOIN(",",A1:A5)Chúng ta có: 1,2,3,4,59. INDEX: Truy xuất giá trị ban đầu hoặc giá trị cuốiVới các dữ liệu cố định, các bảng tính càng hoạt động dễ dàng. Tuy nhiên, khi thêm các dữ liệu mới thường xuyên, chẳng hạn như mỗi tuần thêm dòng dữ liệu mới, sẽ cần phải chỉnh sửa liên tục để duy trì hoạt động của các hàm trong bảng. Ví dụ, hãy tưởng tượng bạn luôn phải tính sự thay đổi giữa ô cuối trong bảng với giá trị phía trước. Và thật khó để có kết quả tính trong ô tương tự ở mỗi lần thay. Dùng hàm LAST dường như là rất thích hợp, nhưng không. Hàm INDEX mới là giải pháp chúng ta cần. Bởi nó cho phép bạn truy tìm giá trị của một ô bằng cách xác định vị trí cột và dòng trong mảng điều kiện. =INDEX(A:A,1,1)Kết hợp INDEX với COUNTA bạn có thể tạo ra một phép tính để truy xuất giá trị cuối cùng của một cột. =INDEX(A:A,COUNTA(A:A),1)10. Bảng PivotBảng Pivot là hàm hữu dụng cuối cùng tôi muốn nhắc tới trong bài này. Bạn có thể mở bảng Pivot bằng cách chọn dữ liệu bạn muốn sử dụng sau đó kích Data > Pivot Table. Báo cáo Pivot giúp dễ dàng nhập, lọc, phân loại và tổng hợp dữ liệu thông qua giao diện người dùng. Ví dụ, với bảng Pivot bạn sẽ có kết quả tương tự giống với CountIF và SumIF cho tất cả các loại quả mà không dùng bất cứ một phép tính nào. Ngoài đếm và tổng hợp, nó có thể tính các giá trị khác như trung bình, phương sai như ảnh dưới đây. Bạn có thể dùng bảng Pivot để tái cấu trúc dữ liệu. Nhìn vào ảnh phía dưới đây, số hóa đơn nằm ở cột đầu tiên với mỗi cột là một loại quả. Sau đó, từng ô chỉ số lượng từng loại theo thứ tự. Bạn có thể áp dụng các hàm Google Sheets nêu trên trong nhiều trường hợp khác nhau. Chắc chắn rằng bạn có thể tìm thấy nhiều tình huống mới để áp dụng các công thức này. Chúng sẽ tiết kiệm thời gian và giúp bạn diễn giải dữ liệu một cách dễ hiểu.
|
