Cách sử dụng công thức trong Google Sheets

Hôm nay tôi sẽ mang các công thức Google Sheets lên bàn. Tôi sẽ bắt đầu với các yếu tố mà chúng bao gồm, nhắc nhở bạn cách tính toán và cho biết sự khác biệt giữa các công thức đơn giản và phức tạp.

Đây là tất cả những gì bạn cần biết:

Bản chất của công thức Google Sheets

Điều đầu tiên trước tiên – để xây dựng một công thức, bạn cần các biểu thức và hàm logic.

Một hàm là một biểu thức toán học; mỗi cái có tên riêng

Để Google Sheets biết bạn sắp nhập một công thức chứ không phải là một số hoặc văn bản, hãy bắt đầu nhập một dấu bằng (=) cho một ô quan tâm. Sau đó, nhập tên hàm và phần còn lại của công thức.

Tiền boa. Bạn có thể kiểm tra danh sách đầy đủ tất cả các chức năng có sẵn trong Google Sheets đây.

Công thức của bạn có thể chứa:

  • tham chiếu ô
  • phạm vi dữ liệu được đặt tên
  • hằng số và văn bản
  • khai thác
  • cac chưc năng khac

Các loại tham chiếu ô

Mỗi chức năng yêu cầu dữ liệu để làm việc và tham chiếu ô được sử dụng để chỉ ra dữ liệu đó.

Để tham chiếu một ô, mã chữ và số được sử dụng – chữ cái cho cột và số cho hàng. Ví dụ, A1 là ô đầu tiên trong cột Một.

Có 3 loại tham chiếu ô Google Sheets:

  • Tương đối: A1
  • Tuyệt đối: $ A $ 1
  • Hỗn hợp (nửa tương đối và nửa tuyệt đối): $ A1 hoặc A $ 1

Ký hiệu đô la ($) là những gì thay đổi loại tham chiếu.

Để chuyển đổi giữa người thân và tuyệt đối nhanh chóng, chỉ cần làm nổi bật bất kỳ tham chiếu ô nào và nhấn F4 trên bàn phím của bạn.

Lúc đầu, tài liệu tham khảo tương đối của bạn – A1 – sẽ thay đổi thành tuyệt đối – $ 1 đô la. nhấn F4 một lần nữa, và bạn sẽ nhận được một tài liệu tham khảo hỗn hợp – 1 đô la. Trên nút nhấn tiếp theo, bạn sẽ thấy $ A1. Một số khác sẽ trả lại mọi thứ về trạng thái ban đầu – A1. Và như thế.

Tiền boa. Để thay đổi tất cả các tham chiếu cùng một lúc, tô sáng toàn bộ công thức và nhấn F4

Sau khi di chuyển, tham chiếu ô tương đối thay đổi theo ô đích. Ví dụ: B1 chứa = A1. Sao chép nó vào C2 và nó sẽ chuyển sang = B2. Vì nó được sao chép 1 cột bên phải và 1 hàng bên dưới, tất cả các tọa độ đã tăng lên 1.

Nếu công thức có tham chiếu tuyệt đối, chúng sẽ không thay đổi khi được sao chép. Chúng luôn chỉ ra một và cùng một ô, ngay cả khi các hàng và cột mới được thêm vào bảng hoặc chính ô đó được dịch chuyển ở một nơi khác.

Công thức gốc trong B1 = A1 = 1 đô la = $ A1 = $ A $ 1
Công thức được sao chép vào C2 = B2 = B $ 1 = $ A2 = $ A $ 1

Do đó, để ngăn các tham chiếu thay đổi nếu được sao chép hoặc di chuyển, hãy sử dụng các tham chiếu tuyệt đối.

Phạm vi dữ liệu

Google Sheets không chỉ sử dụng các tham chiếu ô đơn lẻ mà còn các nhóm ô liền kề – phạm vi. Chúng bị giới hạn bởi các ô trên bên trái và dưới cùng bên phải. Ví dụ, A1: B5 tín hiệu để sử dụng tất cả các ô được tô sáng màu cam bên dưới:
Phạm vi dữ liệu trong Google Sheets.

Các hằng số trong công thức Google Sheets

Các giá trị không đổi trong Google Sheets là những giá trị không thể tính được và luôn giữ nguyên. Thông thường, chúng là số và văn bản, ví dụ 250 (con số), 03/08/2019 (ngày), Lợi nhuận (bản văn). Đây là tất cả các hằng số và chúng ta có thể thay đổi chúng bằng các toán tử và hàm khác nhau.

Ví dụ: công thức có thể chỉ chứa các giá trị và toán tử không đổi:

=30+5*3

Hoặc nó có thể được sử dụng để tính giá trị mới dựa trên dữ liệu của một ô khác:

=A2+500

Đôi khi, mặc dù, bạn phải thay đổi các hằng số bằng tay. Và cách dễ nhất để làm điều đó là đặt từng giá trị vào một ô riêng biệt và tham chiếu chúng trong các công thức. Sau đó, tất cả những gì bạn cần làm là thực hiện các thay đổi trong một ô chứ không phải trong tất cả các công thức.

Vì vậy, nếu bạn đặt 500 đến B2, tham khảo nó với công thức:

=A2+B2

Để có được 700 thay vào đó, chỉ cần thay đổi số trong B2 và kết quả sẽ được tính toán lại.

Toán tử cho công thức Google Sheets

Các toán tử khác nhau được sử dụng trong bảng tính để đặt trước loại và thứ tự tính toán. Họ rơi vào 4 nhóm:

  • toán tử số học
  • Toán tử so sánh
  • toán tử ghép
  • toán tử tham chiếu

Toán tử số học

Như tên cho thấy, chúng được sử dụng để thực hiện các phép tính toán như cộng, trừ, nhân và chia. Kết quả là, chúng tôi nhận được số.

Toán tử số học Hoạt động Thí dụ
+ (dấu cộng) Thêm vào = 5 + 5
– (dấu trừ) Phép trừ

Số âm

= 5-5

= -5

* (dấu hoa thị) Phép nhân = 5 * 5
/ (gạch chéo) Bộ phận = 5/5
% (ký hiệu phần trăm) Phần trăm 50%
^ (dấu hiệu dấu mũ) Số mũ = 5 ^ 2

Toán tử so sánh

Toán tử so sánh được sử dụng để so sánh hai giá trị và trả về một biểu thức logic: TRUE hoặc FALSE.

Toán tử so sánh Điều kiện so sánh Ví dụ về công thức
= = Tương đương với = A1 = B1
> Nhiều hơn = A1> B1
< Ít hơn = A1
> = Nhiều hơn hoặc bằng = A1> = B1
<= Ít hơn hoặc bằng = A1 <= B1
<> Không bằng = A1 <> B1

Toán tử ghép văn bản

Ampersand (&) được sử dụng để kết nối (nối) nhiều chuỗi văn bản thành một. Nhập thông tin bên dưới vào một trong các ô của Google Sheets và nó sẽ trở lại Phi cơ:

="Air"&"craft"

Hoặc, đặt Họ đến A1 và Tên đến B1 và ​​nhận Tên họ văn bản với nội dung sau:

=A1&", "&B1

Toán tử công thức

Các toán tử này được sử dụng để xây dựng các công thức Google Sheets và cho biết phạm vi dữ liệu:

Toán tử công thức Hoạt động Ví dụ về công thức
: (Đại tràng) Toán tử phạm vi. Tạo tham chiếu đến tất cả các ô giữa (và bao gồm) ô đầu tiên và ô cuối cùng được đề cập. B5: B15
, (dấu phẩy) Liên minh điều hành. Tham gia nhiều tài liệu tham khảo thành một. = SUM (B5: B15, D5: D15)
(không gian) Giao lộ điều hành. Tham chiếu giao điểm của hai phạm vi dữ liệu. B7: D7 C6: C8

Tất cả các toán tử có mức độ ưu tiên khác nhau (ưu tiên) xác định thứ tự tính toán công thức và, thường xuyên nhất, ảnh hưởng đến các giá trị kết quả.

Thứ tự tính toán và ưu tiên toán tử

Mỗi công thức trong Google Sheets xử lý các giá trị của nó theo một số thứ tự cụ thể: từ trái sang phải dựa trên quyền ưu tiên của nhà điều hành. Các toán tử có cùng mức độ ưu tiên, ví dụ: phép nhân và phép chia, được tính theo thứ tự xuất hiện của chúng (trái sang phải).

Ưu tiên người vận hành Sự miêu tả
: (Đại tràng)
(không gian)
, (dấu phẩy)
Toán tử phạm vi
Dấu trừ
% Tỷ lệ phần trăm
^ Lũy thừa
* và / Nhân và chia
+ và – Cộng và trừ
& Nối nhiều chuỗi văn bản thành một
= =
<>
<=
> =
So sánh

Cách sử dụng dấu ngoặc để thay đổi thứ tự tính toán

Để thay đổi thứ tự tính toán trong công thức, hãy đặt phần đầu tiên vào dấu ngoặc. Hãy xem nó hoạt động như thế nào.

Giả sử chúng ta có một công thức chuẩn:

=5+4*3

Vì phép nhân dẫn đầu và phép cộng, nên công thức sẽ trả về 17.

Nếu chúng ta thêm dấu ngoặc, trò chơi sẽ thay đổi:

=(5+4)*3

Công thức thêm số đầu tiên, sau đó nhân chúng với 3 và trả về 27.

Các dấu ngoặc từ ví dụ tiếp theo ra lệnh như sau:

=(А2+25)/SUM(D2:D4)

  • tính giá trị cho A2 và thêm nó vào 25
  • tìm tổng các giá trị từ D2, D3 và D4
  • chia số đầu tiên cho tổng giá trị

Tôi hy vọng bạn sẽ không gặp khó khăn gì khi chúng ta tìm hiểu thứ tự tính toán từ khi còn rất nhỏ và tất cả các mỹ phẩm xung quanh chúng ta đều được thực hiện theo cách này. 🙂

Phạm vi được đặt tên trong Google Sheets

Bạn có biết bạn có thể gắn nhãn các ô riêng biệt và toàn bộ phạm vi dữ liệu không? Điều này làm cho việc xử lý bộ dữ liệu lớn nhanh chóng và dễ dàng. Ngoài ra, bạn sẽ tự hướng dẫn mình trong các công thức Google Sheets nhanh hơn nhiều.

Giả sử bạn có một cột nơi bạn tính tổng doanh số trên mỗi sản phẩm và khách hàng. Đặt tên như vậy Total_Sales và sử dụng nó trong công thức.

Tôi tin rằng bạn sẽ đồng ý rằng công thức

=SUM(Total_Sales)

rõ ràng và dễ đọc hơn nhiều

=SUM($E$2:$E$13)

Phạm vi được đặt tên so với phạm vi tham chiếu.

Ghi chú. Bạn không thể tạo phạm vi được đặt tên từ các ô không liền kề.

Để xác định phạm vi của bạn, hãy làm như sau:

  1. Làm nổi bật các ô liền kề của bạn.
  2. Đi đến Dữ liệu> Phạm vi được đặt tên trong menu tờ. Một khung tương ứng sẽ xuất hiện ở bên phải.
  3. Đặt tên cho phạm vi và nhấp Làm xong.

Cách đặt tên cho một phạm vi trong Google Sheets.

Tiền boa. Điều này cũng cho phép bạn kiểm tra, chỉnh sửa và xóa tất cả các phạm vi bạn đã tạo:
Quản lý phạm vi được đặt tên.

Chọn tên chính xác cho phạm vi dữ liệu

Phạm vi được đặt tên làm cho công thức Google Sheets của bạn thân thiện hơn, rõ ràng hơn và dễ hiểu hơn. Nhưng có một bộ quy tắc nhỏ bạn nên tuân theo khi nói đến phạm vi ghi nhãn. Tên:

  • Chỉ có thể chứa các chữ cái, số, dấu gạch dưới (_).
  • Không nên bắt đầu từ một số hoặc từ “đúng” hoặc “sai”.
  • Không được chứa dấu cách () hoặc dấu chấm câu khác.
  • Nên dài 1-250 ký tự.
  • Không nên kiểm đếm với phạm vi chính nó. Nếu bạn cố gắng đặt tên cho phạm vi là A1: B2, các lỗi có thể xảy ra.

Nếu có lỗi xảy ra, ví dụ: bạn sử dụng khoảng trắng trong tên Tổng doanh số, bạn sẽ gặp lỗi ngay lập tức. Tên chính xác sẽ là Tổng số hoặc là Total_Sales.

Ghi chú. Phạm vi được đặt tên của Google Sheets tương tự như tham chiếu ô tuyệt đối. Nếu bạn thêm hàng và cột vào bảng, thì Total_Sales phạm vi sẽ không thay đổi. Di chuyển phạm vi đến bất kỳ vị trí nào của trang tính – và điều này sẽ không làm thay đổi kết quả.

Các loại công thức Google Sheets

Công thức có thể đơn giản và phức tạp.

Các công thức đơn giản chứa các hằng số, tham chiếu đến các ô trên cùng một trang và toán tử. Theo quy định, đó là một hàm hoặc toán tử và thứ tự tính toán rất đơn giản và dễ hiểu – từ trái sang phải:

=SUM(A1:A10)
=A1+B1

Ngay khi các hàm và toán tử bổ sung xuất hiện hoặc thứ tự tính toán trở nên phức tạp hơn một chút, công thức sẽ trở nên phức tạp.

Các công thức phức tạp có thể bao gồm các tham chiếu ô, nhiều hàm, hằng, toán tử và phạm vi được đặt tên. Chiều dài của chúng có thể áp đảo. Chỉ có tác giả của họ có thể “giải mã” chúng một cách nhanh chóng (nhưng thường chỉ khi nó được xây dựng không quá một tuần trước).

Cách đọc các công thức phức tạp một cách dễ dàng

Có một mẹo để làm cho công thức của bạn trông dễ hiểu.

Bạn có thể sử dụng bao nhiêu khoảng trắng và ngắt dòng khi bạn cần. Điều này sẽ không gây rối với kết quả và sẽ sắp xếp mọi thứ theo cách thuận tiện nhất.

Để đặt một dòng ngắt trong công thức, nhấn Alt + Enter trên bàn phím của bạn. Để xem toàn bộ công thức, hãy mở rộng Thanh công thức:
Xây dựng các công thức phức tạp dễ đọc.
Nếu không có các khoảng trắng thừa và các dòng ngắt, công thức sẽ như thế này:

=ArrayFormula(MAX(IF(($B$2:$B$13=B18)*($C$2:$C$13=C18), $E$2:$E$13,"")))

Bạn có thể đồng ý cách đầu tiên là tốt hơn?

Lần tới Tôi sẽ đào sâu hơn vào việc xây dựng và chỉnh sửa các công thức Google Sheets và chúng tôi sẽ thực hành thêm một chút. Nếu bạn có bất kỳ câu hỏi, xin vui lòng để lại trong các ý kiến ​​dưới đây.

Bạn cũng có thể quan tâm:

Source link

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *