Làm cách nào để tìm tất cả các ô có giá trị cụ thể trong Google Trang tính?

Google Trang tính là một công cụ cực kỳ linh hoạt và mạnh mẽ vượt trội (ý định chơi chữ) trong việc sắp xếp và tính toán dữ liệu ở định dạng bảng tính. Dựa trên đám mây, nó cung cấp nhiều khả năng cộng tác, tự động hóa việc thu thập dữ liệu và thậm chí để lấy dữ liệu từ API của bên thứ ba, sử dụng một số chức năng đơn giản nhưng mạnh mẽ

Cho dù bạn chỉ đang bắt kịp tốc độ với Google Trang tính hay đang tìm cách nâng cấp trò chơi bảng tính của mình một cách tổng quát hơn, đây là một số chức năng trong số sáu chức năng Google Trang tính yêu thích của chúng tôi để giúp bạn tận dụng tối đa bảng tính của mình

Tổng quan

Nhấp vào một chức năng Google Trang tính bên dưới để đọc về nó

• Cách nối văn bản trong Google Sheets
• Cách trả về giá trị đầu tiên hoặc cuối cùng trong Google Trang tính bằng hàm INDEX
• Cách nhập dữ liệu vào Google Sheets bằng hàm IMPORT
• Cách dùng VLOOKUP & HLOOKUP nối dữ liệu trong Google Sheets
• Cách ĐẾM và TỔNG các giá trị
• Cách nhóm dữ liệu trong Google Sheets bằng bảng tổng hợp

1. Cách nối văn bản trong Google Sheets

Đôi khi, có thể hữu ích khi sử dụng các ô trong bảng tính của bạn để tạo một đoạn văn bản - có thể để tóm tắt một số giá trị chính hoặc thậm chí tạo một số html

Bằng cách nhập & bạn có thể nối (nối) nội dung của một ô với các ô khác hoặc, sử dụng dấu ngoặc kép, bất kỳ văn bản nào khác mà bạn muốn chèn

Trong ví dụ dưới đây, chúng tôi sẽ sử dụng công thức sau

=A1 & " " & B1 & " and " & A2 & " " & B2

để tạo ra văn bản sau “4 quả táo và 5 quả lê”

Nếu bạn muốn kết hợp nhiều giá trị trong Google Trang tính, chức năng JOIN cũng rất hữu ích. Bạn chỉ định ký tự nào bạn muốn thêm vào giữa các giá trị và các giá trị ô bạn muốn tham gia

Trong ví dụ bên dưới công thức

=join(",",A1:A5)

Sẽ tham gia các giá trị để sản xuất

1,2,3,4,5

2. Cách trả về giá trị đầu tiên hoặc cuối cùng trong Google Trang tính bằng hàm INDEX

Làm việc với bảng tính dễ dàng hơn nhiều khi bạn có tập dữ liệu cố định. Tuy nhiên, nếu bạn đang thêm dữ liệu mới theo định kỳ, có thể là một hàng mới mỗi tuần, thì thường cần phải bảo trì để giữ cho các chức năng của bạn hoạt động

Ví dụ: hãy tưởng tượng bạn luôn muốn tính toán sự thay đổi giữa ô dưới cùng trong bảng tính của mình với giá trị trước đó. Điều này thật khó thực hiện nếu bạn muốn kết quả được tính trong một ô cố định mỗi lần. Không có hàm LAST()

Tuy nhiên, có một giải pháp; . Trong Google Trang tính, công thức INDEX() cho phép bạn trả về giá trị của một ô bằng cách chỉ định hàng và cột sẽ xem xét trong mảng đã chỉ định

Ví dụ: =INDEX(A:A,1,1) sẽ luôn trả về ô đầu tiên trong cột A

Kết hợp INDEX() với COUNTA(), bạn cũng có thể tạo một công thức luôn lấy giá trị cuối cùng trong một cột

=INDEX(A:A,COUNTA(A:A),1)

3. Cách nhập dữ liệu vào Google Sheets bằng hàm IMPORT

Một trong những tính năng bất ngờ hơn của Google Trang tính là chức năng NHẬP

Những thứ này cho phép nó tìm nạp dữ liệu vào bảng tính của bạn từ nhiều nguồn khác nhau, bao gồm XML, HTML, RSS và CSV - hoàn hảo để nhập danh sách các bài đăng trên blog, bản chỉnh sửa, kho sản phẩm hoặc dữ liệu từ một dịch vụ khác. Ví dụ: bạn có thể nhập danh sách các liên kết từ một URL được chỉ định bằng

=IMPORTXML("https://en.wikipedia.org/wiki/Moon_landing", "//a/@href")

Nhập nội dung của danh sách hoặc bảng từ một URL được chỉ định bằng cách sử dụng

=IMPORTHTML("http://en.wikipedia.org/wiki/Demographics_of_India", "table", 4)

Nhập nguồn cấp dữ liệu RSS hoặc Atom bằng cách sử dụng

=IMPORTFEED("http://news.google.com/?output=atom")

Nhập nội dung của tệp CSV từ một URL được chỉ định bằng cách sử dụng

________số 8

Cùng với các tính năng tự động hóa và viết kịch bản của Google Trang tính, các hàm IMPORT trở thành một công cụ rất mạnh để thu thập và cấu trúc dữ liệu từ các nguồn công khai. Sử dụng có trách nhiệm

Để biết thêm các cách đưa dữ liệu của bạn vào Google Trang tính, hãy xem 4 cách của chúng tôi để tự động nhập dữ liệu trực tiếp vào bảng tính trong bài đăng

4. Cách dùng VLOOKUP & HLOOKUP nối dữ liệu trong Google Sheets

Các chức năng LOOKUP của Google Trang tính được giải thích rõ nhất bằng một ví dụ. Tóm lại, chúng cho phép bạn tìm kiếm kết quả khớp cho một từ cụ thể (hoặc ID khác) rồi đọc giá trị từ một hàng hoặc cột tương ứng. Điều này có thể thực sự hữu ích nếu bạn có các bộ dữ liệu khác nhau cho cùng một đối tượng trong bảng tính của mình, chẳng hạn như một số hàng dữ liệu về một sản phẩm, cá nhân hoặc dự án riêng lẻ

Trong ví dụ bên dưới, hãy tưởng tượng bạn muốn theo dõi sự thay đổi về số lượng Táo, Cam và Lê trong khoảng thời gian từ tháng 1 đến tháng 2. Thứ tự của các loại trái cây và những loại trái cây có sẵn thay đổi mỗi tháng, do đó, một phép trừ ô cố định đơn giản sẽ không hiệu quả

Thay vào đó, với VLOOKUP(), bạn có thể yêu cầu Google Trang tính tìm kiếm theo chiều dọc thông qua một tập hợp dữ liệu cho đến khi tìm thấy từ khớp với từ bạn đang tìm kiếm, sau đó đọc theo chiều ngang để tìm giá trị tương ứng trong cột liền kề. VLOOKUP là viết tắt của Tra cứu theo chiều dọc vì nó tìm kiếm theo chiều dọc cho từ rồi theo chiều ngang cho giá trị trong khi HLOOKUP là viết tắt của Tra cứu theo chiều ngang vì nó tìm kiếm theo chiều ngang cho từ và sau đó theo chiều dọc cho giá trị

Hãy bắt đầu với VLookup bằng cách sử dụng bảng bên dưới và sử dụng công thức

=join(",",A1:A5)0

Trong ngoặc, “F2” đang yêu cầu VLOOKUP tìm giá trị “Táo” mà bạn tìm thấy trong ô F2. Bởi vì nó là VLOOKUP, nên phần của công thức =join(",",A1:A5)1 yêu cầu Google trang tính tìm kiếm theo chiều dọc trong dữ liệu tháng 1 (các ô từ A2 đến B6) để tìm giá trị Apple. Số “2” trong công thức cho VLOOKUP biết rằng, khi tìm thấy “Apple”, nó sẽ tìm giá trị trong cột thứ hai. "Sai" đang nói với nó rằng, nếu nó không tìm thấy giá trị, thì nó không nên làm gì cả. Cuối cùng, công thức này đang tìm kiếm Táo trong tập dữ liệu tháng 1 và sau đó trả về giá trị 1003 trong cột thứ hai

Để tính toán sự thay đổi, tra cứu cho tháng 2 cần được trừ đi từ tra cứu cho tháng 1, vì vậy chúng tôi thêm vào công thức như sau

=join(",",A1:A5)2

Phần thứ hai của công thức này giống như trên, nhưng bây giờ chúng tôi đang thêm dữ liệu tháng 2 và yêu cầu Google Trang tính trừ dữ liệu tháng 1 khỏi nó. Vì vậy, bằng cách sử dụng công thức tương tự như tháng 1 và thay đổi =join(",",A1:A5)1 cho =join(",",A1:A5)4, chúng tôi đang yêu cầu Google Trang tính thực hiện cùng một hàm VLOOKUP, nhưng cho tháng 2. Đây là nói. Xem số táo đã được mua trong tháng 2 (785) và trừ đi số táo đã được mua trong tháng 1 (1003), trả về giá trị -218

Tương tự, =join(",",A1:A5)5 thực hiện chức năng tương tự nhưng đọc ngang rồi nhìn xuống khi khớp. Thông tin thêm về cả hai chức năng có thể được tìm thấy ở đây

1. Hàm VLOOKUP trong Google Trang tính
2. Hàm HLOOKUP trong Google Trang tính
   
   

Có một số hạn chế với VLOOKUP và HLOOKUP. Nếu bạn thích mạo hiểm, hãy xem bài viết này về cách kết hợp MATCH với INDEX để tra cứu hiệu quả hơn nữa trong Google Trang tính

5. Cách đếm và cộng các giá trị nếu chúng chứa một giá trị nhất định trong Google Sheets

Các công thức =join(",",A1:A5)6 và =join(",",A1:A5)7 dễ tìm hiểu hơn một chút so với CÔNG THỨC TÌM KIẾM. Họ làm những gì họ nói - họ cho phép bạn tính tổng hoặc đếm các giá trị nếu chúng phù hợp với một số tiêu chí

Trong ví dụ bên dưới, bạn có thể đếm số lượng Táo đã bán bằng công thức như

=join(",",A1:A5)8

Đã nói. Đếm số lần xuất hiện của từ “Apple” trong các ô từ B2 đến B10

Bạn cũng có thể tính tổng trọng lượng Táo đã bán bằng cách sử dụng hàm =join(",",A1:A5)9

1,2,3,4,50

Chúng tôi đang yêu cầu Google Trang tính tìm kiếm các đề cập đến từ “Apple” trong cột B và tính tổng giá trị của các ô tương ứng trong cột C

6. Cách nhóm dữ liệu trong Google Sheets bằng bảng tổng hợp

Bảng tổng hợp là một trong những tính năng mạnh mẽ nhất trong bất kỳ ứng dụng bảng tính nào và việc triển khai Google Trang tính đặc biệt tốt. Bạn có thể mở bảng tổng hợp bằng cách chọn dữ liệu bạn muốn sử dụng rồi nhấp vào Dữ liệu >> Bảng tổng hợp

Báo cáo bảng tổng hợp giúp bạn dễ dàng nhóm, lọc, sắp xếp và tóm tắt dữ liệu của mình bằng trình chỉnh sửa trong Google Trang tính, thay vì sử dụng công thức phức tạp. Ví dụ: với các bảng tổng hợp, bạn có thể làm điều tương tự mà chúng ta vừa làm với 1,2,3,4,51 và =join(",",A1:A5)9 cho tất cả các loại trái cây mà không phải sử dụng bất kỳ công thức nào. Ngoài việc đếm và tính tổng, bạn cũng có thể dễ dàng tính các giá trị khác như giá trị trung bình và phương sai như bạn có thể thấy trong ví dụ bên dưới. Bạn cũng có thể xem Google Trang tính thực tế mà chúng tôi đang sử dụng tại đây

Bạn cũng có thể sử dụng bảng tổng hợp để cơ cấu lại dữ liệu của mình. Dưới đây, số hóa đơn được hiển thị trong cột đầu tiên với một cột cho từng loại trái cây. Mỗi ô sau đó hiển thị số lượng của từng loại theo từng thứ tự

Những ví dụ này chỉ đề cập đến những gì bạn có thể làm với bảng tổng hợp. Bạn có thể tìm thêm nhiều trợ giúp về bảng tổng hợp trong trung tâm hỗ trợ của Google Trang tính

Nếu bạn bị cuốn hút vào các bảng tổng hợp, chức năng QUERY cũng rất đáng xem. Nó có đường cong học tập dốc hơn (không có trình chỉnh sửa đẹp mắt nào để chơi) nhưng nó cho phép bạn chạy một số truy vấn cơ bản giống như SQL

Blog Coding Is For Losers có phần giới thiệu tuyệt vời về chức năng QUERY

Các bài viết hữu ích khác trên Google Trang tính

• Thêm 3 mẹo Google Trang tính giúp cuộc sống của bạn dễ dàng hơn
• 5 chuyên gia Google Sheets hàng đầu để theo dõi
• Cách nhập giá chứng khoán vào Google Sheets bằng GOOGLEFINANCE
• Cách tạo đồng hồ đếm ngược bằng Google Sheets

Ngoài Google Trang tính. trực quan hóa và chia sẻ dữ liệu của bạn

Bạn muốn đưa trò chơi bảng tính của mình đi xa hơn nữa?

Tìm hiểu thêm về ví dụ bảng điều khiển bảng tính này tại đây

Ví dụ trên chứa nhiều hình ảnh trực quan khác nhau, có thể được cung cấp bởi Google Trang tính hoặc bảng tính Excel, tự động cập nhật để hiển thị dữ liệu cập nhật từng phút

Làm cách nào tôi có thể chọn tất cả các ô chứa văn bản cụ thể trong Google Trang tính?

Làm cách nào để liệt kê tất cả các phiên bản trùng khớp của một giá trị trong Google Trang tính?