Tìm hiểu cách đối chiếu dữ liệu đã nhập với dữ liệu được nhập theo cách thủ công trong Google Trang tính sẽ hữu ích trong việc xử lý dữ liệu đã nhập một cách hiệu quả.
Làm việc với dữ liệu đã nhập sẽ luôn là một quá trình động, vì bất kỳ thay đổi nào bạn thực hiện đối với dữ liệu gốc sẽ ảnh hưởng đến bảng tính của bạn. Điều này có thể gây ra sự cố, đặc biệt là khi làm việc với cả dữ liệu được nhập và được nhập thủ công. Vì hầu hết các chương trình bảng tính sử dụng công thức phân biệt vị trí, bất kỳ thay đổi nào do cập nhật dữ liệu nguồn có thể gây ra lỗi và cuối cùng làm hỏng bảng tính.
Trong bài viết này, chúng ta sẽ tìm hiểu cách đối sánh dữ liệu đã nhập với dữ liệu được nhập theo cách thủ công trong Google Trang tính, cho dù dữ liệu nguồn là từ một tệp Google Trang tính khác hay từ một trang tính khác trong cùng một tệp.
Hãy xem xét ví dụ này.
Điều quan trọng là chủ cửa hàng tạp hóa phải theo dõi doanh số bán hàng của họ và kiểm kê hàng còn lại thường xuyên nhất có thể. Vì vậy, bạn đang yêu cầu nhân viên của mình ghi lại doanh thu hàng ngày và hàng tồn kho hàng ngày trong một tệp bảng tính. Dưới đây là một ví dụ về một mục hoàn chỉnh.
Bạn quyết định tạo một bảng tính riêng để nhập dữ liệu từ bảng tính này để tính lợi nhuận thu được từ việc bán từng sản phẩm. Trong bảng mới, bạn cũng quyết định theo dõi doanh số bán hàng của những ngày trước đó để xác định xem nên loại bỏ hoặc thay thế một số sản phẩm bạn bán. Ngoài ra còn có một cột cho một số nhận xét để xem xét cho mỗi sản phẩm.
Cài đặt này là đủ cho vài ngày đầu tiên sử dụng; tuy nhiên sẽ có những ngày phiếu nhập sẽ bị xếp lại (ví dụ hết sản phẩm thì nhân viên sẽ không ghi cho ngày đó, đơn hàng khác sản phẩm). Do đó, tất cả các phép tính và ghi chú bạn đã thực hiện trong một bảng tính riêng biệt sẽ bị rối tung. Bạn có thể làm gì để sửa lỗi này?
Trong hướng dẫn này, chúng tôi sẽ sử dụng các ID duy nhất để nhóm từng hàng dữ liệu như một phần của một bản ghi. Các số nhận dạng duy nhất này sẽ được sử dụng để liên kết dữ liệu đã nhập với dữ liệu được nhập theo cách thủ công. Do đó, chúng tôi sẽ thực hiện một số thay đổi đối với các bảng tính hiện có này để chuẩn bị đối chiếu dữ liệu đã nhập với dữ liệu được nhập theo cách thủ công.
Tạo số nhận dạng duy nhất
Trong bảng đầu tiên, được gọi là “SF1” ở đây, chúng tôi sẽ thêm một cột để chỉ định ID hàng tồn kho cho từng mặt hàng thực phẩm. Bạn có thể sử dụng bất kỳ tổ hợp ký tự nào miễn là mỗi số nhận dạng là duy nhất cho mỗi mục nhập.
Đối với bảng tính thứ hai, mà bây giờ chúng ta sẽ gọi là “SF2”, trước tiên chúng ta hãy bắt đầu làm việc trên một trang tính riêng biệt trong cùng một tệp. Điều này cho phép chúng tôi giữ cho dữ liệu đã nhập và được nhập theo cách thủ công nhất quán khi chúng tôi thực hiện một số thay đổi. Tương tự như SF1, hãy tạo một cột chứa các ID duy nhất này cho SF2. Nó có thể theo bất kỳ thứ tự nào, nhưng phải chứa tất cả các ID khoảng không quảng cáo.
Vì chúng tôi đã chọn các số làm định danh, chúng tôi có thể sắp xếp chúng theo thứ tự tăng dần. Bạn cũng có thể thêm một vài dòng số nhận dạng bổ sung có thể được sử dụng trong tương lai.
Để điều chỉnh các tập dữ liệu này, chúng tôi sẽ phụ thuộc vào một số chức năng: các hàm VLOOKUP, IMPORTRANGE, IFERROR, cũng như ARRAYFORMULA. Chúng ta sẽ chỉ thảo luận chi tiết về hai tính năng đầu tiên vì chúng là quan trọng nhất đối với nhiệm vụ hiện tại, trong khi hai tính năng cuối cùng hoạt động giống như các phím tắt để thực hiện nhiệm vụ dễ dàng hơn. Theo các liên kết đến các chức năng này để thảo luận chi tiết hơn.
Bây giờ trước tiên chúng ta hãy tìm hiểu thêm về tính năng VLOOKUP trong Google Trang tính!
Các tính năng của hàm Vlookup
Vì vậy, cú pháp (cách chúng tôi viết) của hàm Vlookup như sau:
= VLOOKUP (khóa_ tìm kiếm, phạm vi, chỉ mục, [is_sorted])
Hãy phân tích nó và hiểu ý nghĩa của từng thuật ngữ sau:
- = Dấu bằng là cách chúng tôi chạy bất kỳ chức năng nào trong Google Trang tính.
- VLOOKUP () là hàm Vlookup (VLOOKUP) của chúng ta. Nó tìm kiếm một khóa trong cột đầu tiên của phạm vi và trả về giá trị của ô được chỉ định trong hàng được tìm thấy.
- khóa_tìm_kiếm là giá trị cần tìm kiếm.
- range (phạm vi) là một mảng hoặc dải ô cần xem xét khi tìm kiếm. Cột đầu tiên của phạm vi được tìm kiếm cho khóa được chỉ định trong search_key.
- index là chỉ số cột của giá trị trả về, trong đó cột đầu tiên trong phạm vi được đánh số 1.
- is_sorted cho biết liệu cột có được sắp xếp cho tìm kiếm hay không. Đây là một tham số tùy chọn và mặc định là TRUE. Khi is_sorted là TRUE, kết quả phù hợp nhất được trả về. Tuy nhiên, bạn nên đặt is_sorted thành FALSE để trả về kết quả khớp chính xác.
Hàm VLOOKUP sẽ chịu trách nhiệm khớp các ID duy nhất của mỗi mục nhập với chuỗi chính xác trong SF2. Hàm này khớp các số nhận dạng duy nhất (khóa_ tìm kiếm) trong SF2 với các số nhận dạng trong SF1 (dải ô). Sau đó, nó trả về các giá trị được tìm thấy trên cùng một dòng. Số chỉ mục cho biết dữ liệu nào sẽ được trả về: 2 – Sản phẩm, 3 – Danh mục, 4 – Số lượng trong kho, 5 – Doanh số.
Vì chúng tôi đang làm việc với dữ liệu được nhập từ một bảng tính khác trong ví dụ này, chúng tôi sẽ phải sử dụng hàm IMPORTRANGE trong tham số phạm vi. Như tên cho thấy, chức năng này cho phép chúng ta nhập một phạm vi hoặc mảng từ một tệp khác.
Bây giờ chúng ta hãy tìm hiểu thêm về hàm IMPORTRANGE trong Google Trang tính!
Đặc điểm của hàm IMPORTRANGE
Vì vậy, cú pháp (như chúng ta viết) của hàm IMPORTRANGE như sau:
= IMPORTRANGE (spreadhseet_url, range_string)
Hãy phân tích nó và hiểu ý nghĩa của từng thuật ngữ sau:
- = Dấu bằng là cách chúng tôi chạy bất kỳ chức năng nào trong Google Trang tính.
- IMPORTRANGE () là hàm IMPORTRANGE của chúng tôi. Nó nhập một loạt các ô từ bảng tính được chỉ định.
- Spreadheet_url là URL của bảng tính mà từ đó dữ liệu sẽ được nhập. Giá trị này có thể được đặt trong dấu ngoặc kép hoặc có thể là tham chiếu ô chứa URL.
- range_string phải là một giá trị chuỗi chỉ định dải ô cần nhập. Nó phải theo định dạng:[имя_листа!] Phạm vi “(ví dụ:” Sheet1! A1: B3 “hoặc” A1: B3 “). Nếu tên trang tính không được chỉ định, hàm IMPORTRANGE sẽ nhập trang tính đầu tiên từ phạm vi đã cho. Giá trị range_string có thể được đặt trong dấu ngoặc kép hoặc nó có thể là tham chiếu đến ô chứa URL.
Bây giờ chúng ta đã tìm hiểu về các hàm VLOOKUP và IMPORTRANGE, chúng ta có thể khớp khá nhiều dữ liệu đã nhập với dữ liệu được nhập theo cách thủ công trong Google Trang tính. Tuy nhiên, chỉ sử dụng hai hàm này sẽ yêu cầu chúng ta nhập một công thức vào mỗi ô của bảng tính để nhập dữ liệu từ đó. Để làm cho nhiệm vụ này dễ dàng hơn, chúng tôi sẽ sử dụng hai chức năng bổ sung đã đề cập trước đó.
Sử dụng hàm ARRAYFORMULA cho phép chúng ta nhập công thức chỉ cho một ô và nó sẽ trả về giá trị cho nhiều ô. Hàm IFERROR cho phép chúng ta loại bỏ các lỗi xảy ra khi sử dụng một phạm vi trong công thức có chứa khoảng trắng. Điều này dẫn đến công thức chung:
= ARRAYFORMULA (IFERROR (VLOOKUP (ID duy nhất, IMPORTRANGE (URL SF1, Dữ liệu đã nhập), chỉ mục, FALSE)))
Giờ đây, chúng tôi cuối cùng đã sẵn sàng để căn chỉnh dữ liệu đã nhập với dữ liệu được nhập theo cách thủ công trong Google Trang tính!
Cách đối chiếu dữ liệu đã nhập với dữ liệu được nhập thủ công trong Google Trang tính
Các bước trong phần này của hướng dẫn áp dụng cho dữ liệu đã nhập từ tệp bảng tính khác. Một số lưu ý sẽ được thảo luận ở phần sau về cách tùy chỉnh các bước bên dưới cho dữ liệu được nhập từ cùng một tệp nhưng trên một trang tính riêng biệt.
Hãy xem kịch bản đã thảo luận trước đó.
- Tạo ID duy nhất cho mỗi mục nhập trong cả hai bảng của bạn. Chỉnh sửa các thuộc tính chia sẻ SF1. Đảm bảo Bất kỳ ai có liên kết đều được chọn và không bị “Hạn chế”.
- Trong SF2, chỉ cần nhấp vào bất kỳ ô nào để làm cho nó hoạt động. Đối với hướng dẫn này, tôi sẽ chọn B2 nơi tôi muốn hiển thị kết quả của mình. Sau đó nhập công thức: = ARRAYFORMULA (IFERROR (VLOOKUP (
- Tiếp theo, chọn cột đầu tiên làm tham số SEARCH_KEY hoặc chỉ nhập A2: A. Sau đó, nhập dấu phẩy ‘,’ để cho biết rằng chúng tôi muốn chuyển sang tham số tiếp theo. Nếu thực hiện đúng, cột đầu tiên sẽ được tô sáng ngoại trừ tiêu đề.
- Đối với tham số phạm vi, chúng tôi sẽ sử dụng hàm IMPORTRANGE. Nhập IMPORTRANGE hoặc chọn một chức năng khi bạn nhập. Đặt liên kết SF1 trong dấu ngoặc kép để nhập tham số bảng tính_url. Sau đó chỉ định phạm vi chứa dữ liệu cần nhập. Trong ví dụ này, hãy nhập “Khoảng không quảng cáo! A2: E ”để chọn 5 cột đầu tiên. Cuối cùng, đóng tham số phạm vi bằng dấu ngoặc đơn và dấu phẩy. * Lưu ý: “Inventor” trong công thức đề cập đến tên của trang tính trong SF1 mà bạn muốn lấy dữ liệu từ đó.
- Đối với tham số chỉ mục, hãy chỉ định số cột bạn muốn nhập. Trong ví dụ này, chúng tôi muốn trích xuất tất cả dữ liệu trong phạm vi được chỉ định. Nhập {2,3,4,5} theo sau bởi dấu phẩy. Cột đầu tiên không được chọn vì nó đề cập đến các số nhận dạng duy nhất. Nhập FALSE cho tham số is_sorted.
- Nhấn Enter để hoàn thành công thức. Trả lại dữ liệu trước đó về các căn chỉnh thích hợp.
Cuối cùng, đây là một số điều bạn nên chú ý:
- Điều quan trọng là các số nhận dạng được sử dụng là duy nhất cho mỗi mục nhập và được sử dụng một cách chính xác và nhất quán để tránh các lỗi trong tương lai.
- Trong SF2, các cột từ B đến F bao gồm dữ liệu đã nhập. Bạn nên tránh nhập dữ liệu theo cách thủ công cho các cột này, vì điều này sẽ dẫn đến lỗi.
- Ngay cả khi một số số nhận dạng trong SF1 bị xóa hoặc bị thiếu, thì số nhận dạng tương tự sẽ vẫn tồn tại trong SF2, nhưng các cột chứa dữ liệu đã nhập sẽ trống.
- Đối với dữ liệu đã nhập từ cùng một tệp, chỉ cần thay thế hàm IMPORTRANGE bằng một phạm vi ở định dạng phạm vi [имя_листа!] . Sau đó, công thức chung sẽ là:
= ARRAYFORMULA (IFERROR (VLOOKUP (ID duy nhất,[sheet_name]! phạm vi, chỉ mục, FALSE)))
Xong! Thật dễ dàng để đối chiếu dữ liệu đã nhập với dữ liệu được nhập thủ công, phải không?
Ngoài ra, hãy xem nhiều công thức Google Trang tính khác để tạo ra những công thức mạnh mẽ hơn nữa có thể giúp cuộc sống của bạn dễ dàng hơn rất nhiều.
Mục tiêu của chúng tôi là tạo ra nhiều hướng dẫn chi tiết và ngắn gọn cho những người dùng Google Trang tính như bạn.
