Công Nghệ Google

Gán lệnh thực thi cho 1 ô trong sheet google

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. ARRAYFORMULA

Hà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:

Trang tính trở nên chậm vì các hàm riêng lẻ trong hàng nghìn ô.
Thực hiện bất kỳ thay đổi nào trong công thức sẽ cần hàng giờ chỉnh sửa.
Sao chép và dán các công thức không theo mảng vào các ô được chỉ định là một quá trình mệt mỏi.

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à HLOOKUP

Vớ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. IMPORTRANGE

IMPORTRANGE 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 Sheets

Mộ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["//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["//en.wikipedia.org/wiki/Demographics_of_India", "table", 4]

Nhập RSS hoặc Atom:

=IMPORTFEED["//news.google.com/?output=atom"]

Nhập các nội dung của tâp tin CSV:

=IMPORTDATA["//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. LEN

Hà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. IFERROR

Bả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 Sheets

SUMIF 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:

=VLOOKUP[F2,$A$2:$B$6,2,false]

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.

=VLOOKUP[F2,$A$2:$B$6,2,false]

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 Sheets

Hà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:

=VLOOKUP[F2,$A$2:$B$6,2,false]

và 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ụ:

=VLOOKUP[F2,$A$2:$B$6,2,false]

Chúng ta có:

=VLOOKUP[F2,$A$2:$B$6,2,false]

9. INDEX: Truy xuất giá trị ban đầu hoặc giá trị cuối

Vớ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.

=VLOOKUP[F2,$A$2:$B$6,2,false]

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.

=VLOOKUP[F2,$A$2:$B$6,2,false]

10. Bảng Pivot

Bả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.

9 hàm Google Sheets cơ bản bạn nên biết
Cách viết ghi chú, bình luận trên Google Sheets
Cách tạo biểu đồ thanh ngang trong Google Sheets
Cách đếm số từ trên Google Sheets

Thứ Ba, 16/11/2021 23:00

4,6 ★ 9 👨 59.391

#Hàm Vlookup # #Hàm SumIf

0 Bình luận

Sắp xếp theo

Xóa Đăng nhập để Gửi

Bạn nên đọc

Tự động kiểm tra chính tả trong Internet Explorer 9 với Speckie
Cổ phiếu Apple giảm nhẹ sau cái chết của Steve Jobs
Cách dùng xe bay Glider trong Rules of Survival
HP kiện Oracle ngưng hỗ trợ chip Itanium
Tâm sự của một chuyên gia test thiết bị âm thanh
Google Nexus 7 cho iPad hít khói tại Nhật

Google Sheets

Mời tải miễn phí các slide PowerPoint và Google Slides cực đẹp được thiết kế bởi Google
Cách chuyển cột thành hàng trong Google Sheets và ngược lại
Cách xoay văn bản trong Google Sheets
9 hàm Google Sheets cơ bản bạn nên biết
Cách khóa và hiện công thức Google Sheets
Cách tự động đổi màu ô trong Google Sheets
Cách vẽ biểu đồ đường trong Google Sheets
Cách sắp xếp dữ liệu, bảng tính theo ngày trên Google Sheets
Cách tạo định dạng ngày giờ tùy chỉnh trong Google Sheets

Xem thêm

Tin học Văn phòng

Microsoft Word
Microsoft Excel
Microsoft PowerPoint
Microsoft Access
Google Docs
Google Sheets
Google Slides
Tài liệu PDF
Ứng dụng khác
Thủ thuật Email

Công nghệ
- Ứng dụng
- Hệ thống
- Game - Trò chơi
- iPhone
- Android
- Linux
- Nền tảng Web
- Đồng hồ thông minh
- Chụp ảnh - Quay phim
- macOS
- Phần cứng
- Thủ thuật SEO
- Kiến thức cơ bản
- Raspberry Pi
- Dịch vụ ngân hàng
- Lập trình
- Dịch vụ công trực tuyến
- Dịch vụ nhà mạng
- Nhà thông minh
Download
- Ứng dụng văn phòng
- Tải game
- Tiện ích hệ thống
- Ảnh, đồ họa
- Internet
- Bảo mật, Antivirus
- Họp, học trực tuyến
- Video, phim, nhạc
- Mail
- Lưu trữ đám mây
- Giao tiếp, liên lạc, hẹn hò
- Hỗ trợ học tập
- Máy ảo
Tiện ích
Khoa học
- Khoa học vui
- Khám phá khoa học
- Bí ẩn - Chuyện lạ
- Chăm sóc Sức khỏe
- Khoa học Vũ trụ
- Khám phá thiên nhiên
Điện máy
- Tủ lạnh
- Tivi
- Điều hòa
- Máy giặt
Cuộc sống
- Kỹ năng
- Món ngon mỗi ngày
- Làm đẹp
- Nuôi dạy con
- Chăm sóc Nhà cửa
- Kinh nghiệm Du lịch
- Halloween
- Mẹo vặt
- Giáng sinh - Noel
- Tết 2023
- Quà tặng
- Giải trí
- Là gì?
- Nhà đẹp
- TOP
- Phong thủy
Video
- Công nghệ
- Cisco Lab
- Microsoft Lab
- Video Khoa học
Ô tô, Xe máy
- Giấy phép lái xe
Làng Công nghệ
- Tấn công mạng
- Chuyện công nghệ
- Công nghệ mới
- Trí tuệ nhân tạo [AI]
- Anh tài công nghệ
- Bình luận công nghệ
- Tổng hợp
Học CNTT
- Quiz công nghệ
- Microsoft Word 2016
- Microsoft Word 2013
- Microsoft Word 2007
- Microsoft Excel 2019
- Microsoft Excel 2016
- Microsoft PowerPoint 2019
- Microsoft PowerPoint 2016
- Google Sheets - Trang tính
- Photoshop CS6
- Photoshop CS5
- HTML
- CSS và CSS3
- Python
- Học SQL
- Lập trình C
- Lập trình C++
- Lập trình C#
- Học HTTP
- Bootstrap
- SQL Server
- JavaScript
- Học PHP
- jQuery
- Học MongoDB
- Unix/Linux
- Học Git
- NodeJS

Giấy phép số 362/GP-BTTTT. Bộ Thông tin và Truyền thông cấp ngày 30/06/2016. Cơ quan chủ quản: CÔNG TY CỔ PHẦN MẠNG TRỰC TUYẾN META. Địa chỉ: 56 Duy Tân, Dịch Vọng Hậu, Cầu Giấy, Hà Nội. Điện thoại: 024 2242 6188. Email: info@meta.vn. Chịu trách nhiệm nội dung: Lê Ngọc Lam.