Tôi đưa ra cho bạn 10 công thức tùy chỉnh Google Spreadsheet mà tôi tin rằng sẽ hữu ích cho bạn trong việc giảm bớt công việc văn phòng hàng ngày.
Nếu bạn chỉ có kiến thức cơ bản về Google Sheets, bạn chắc chắn sẽ thấy những công thức tùy chỉnh này của Google Sheets hữu ích.
Bạn có thể sử dụng nó ngay lập tức bằng cách làm theo các mẹo mà không cần biết mã / hàm nào đang sử dụng.
Có Có Bất Kỳ Hướng Dẫn Miễn Phí Nào Để Học Công Thức Tính Bảng Tính Google Không?
Tất nhiên, bạn có thể tìm thấy rất nhiều trên mạng và có thể miễn phí / trả phí. Nhưng tôi có khoảng 500 công thức trở lên dưới bản ghi Tính toán bảng tính trên trang web này. Tất cả đều được mở khóa và không yêu cầu đăng ký hoặc đăng ký để truy cập!
Ngoài ra, tôi đã đề cập đến hầu hết các chức năng trong Google Sheets bằng các ví dụ đơn giản và dễ hiểu. Có một Chỉ mục các chức năng trong Google Sheets để giúp bạn dễ dàng điều hướng qua các chức năng.
Trong hướng dẫn này, tôi chia sẻ với bạn 10 công thức Google Spreadsheets hữu ích nhất.
Công thức tính Google Spreadsheets cho Số thứ tự hàng tự động
Như bạn có thể đã thấy, cột số thứ tự là một phần của nhiều định dạng chính thức. Trong văn phòng của bạn cũng vậy, bạn đã thấy hoặc đang sử dụng các định dạng bắt đầu bằng số thứ tự.
Bạn có thể tự động hóa điều này trong Google Spreadsheet bằng một công thức tùy chỉnh.
=ArrayFormula (dòng(A1:A20))
Công thức này sẽ tạo ra các số từ 1 đến 20 theo tuần tự. Chỉ cần áp dụng nó vào bất kỳ ô nào. Bạn sẽ thấy các số từ 1 đến 20 được điền vào cột đó xuống dưới.
Thay đổi tham chiếu ô A20 trong công thức thành số bạn muốn. Nếu bạn đặt 50, số thứ tự sẽ từ 1 đến 50.
Nếu bạn nhìn thấy lỗi #REF! đừng hoảng loạn! Nó nói rằng;
Kết quả Mảng không được mở rộng vì nó sẽ ghi đè dữ liệu trong ...
Lý do là không có đủ ô trống cho công thức này điền vào. Vì vậy, hãy đảm bảo rằng các ô dưới trống.
Tìm hiểu thêm chi tiết về chủ đề này: Số thứ tự tự động trong Google Sheets với Hàm Row
Công thức tính Google Spreadsheets để So sánh hai cột để Tìm sự Khác biệt
Đây là một công việc phổ biến khác trong văn phòng, tức là so sánh hoặc so khớp hai danh sách.
Ví dụ:
Tôi đã chuẩn bị một danh sách rau củ và sau đó đặt hàng với một nhà cung cấp – Đây là ‘Danh sách 1’ của tôi.
Sau đó, tôi đã nhận được các mặt hàng đã đặt hàng – Danh sách 2.
Làm thế nào để tôi kiểm tra xem tất cả các mặt hàng đã được đặt hàng đã được nhận?
Nếu cả hai danh sách đều có trong cùng một thứ tự chữ cái hoặc từng mục, bạn có thể so sánh nó cạnh nhau. Nhưng nó có thể hoặc không hoặc được sắp xếp theo thứ tự như vậy.
Đây là công thức Google Spreadsheet tùy chỉnh của tôi để so sánh hoặc so khớp hai danh sách. Nó hoạt động tốt với danh sách của bạn. Không quan trọng cách các mục được sắp xếp trong hai danh sách.
=ArrayFormula (if (iferror (match (A2: A10, B2: B10, 0)) = "", "Không tìm thấy" , "Tìm thấy"))
Thay đổi phạm vi A10 và B10 thành ô cuối cùng trong danh sách của bạn.
Trong công thức Google Spreadsheet tùy chỉnh này, tôi đã sử dụng hàm So khớp. Có một giải pháp khác sử dụng REGEX – So khớp hai cột chứa giá trị không theo bất kỳ thứ tự nào bằng Regex.
Công thức Google Sheets giết chết để tính toán ngày làm việc ròng loại trừ ngày cuối tuần và ngày nghỉ chính thức
Bạn có làm việc trong HR / ADMIN / Kế toán không?
Công thức dưới đây có thể giúp bạn đưa ra quyết toán cuối cùng không có lỗi của nhân viên của mình. Ngoài ra, nó có thể tiết kiệm một vài phút của bạn chắc chắn.
Làm thế nào để Tính toán Số giờ Làm việc của Nhân viên?
Chúng tôi sẽ sử dụng hàm NETWORKDAYS.INTL. Điều bạn muốn làm là;
Nhập ngày bắt đầu (ngày tham gia nhân viên) vào một ô (A2) và ngày kết thúc (ngày rời đi nhân viên) vào một ô khác (B2).
Trong cột tiếp theo (Cột C), nhập danh sách ngày nghỉ chính thức của bạn trong thời gian đó. Nếu bạn muốn, bạn cũng có thể bao gồm các ngày vắng mặt của nhân viên trong danh sách nghỉ lễ của mình.
Công thức:
=NETWORKDAYS.INTL (A2, B2, 1, C2: C15)
Ví dụ:
Trong công thức này, cuối tuần là THỨ BẢY / CHỦ NHẬT. Số 1 trong công thức chỉ điều này. Tôi biết cuối tuần của bạn có thể khác. Bạn có thể chọn số ngày cuối tuần mong muốn từ danh sách dưới đây.
2 = Chủ nhật / Thứ 2, 3 = Thứ 2 / Thứ 3, 4 = Thứ 3 / Thứ 4, 5 = Thứ 4 / Thứ 5, 6 = Thứ 5 / Thứ 6, 7 = Thứ 6 / Thứ 7.
11 = Chủ nhật, 12 = Thứ 2, 13 = Thứ 3, 14 = Thứ 4, 15 = Thứ 5, 16 = Thứ 6, 17 = Thứ 7
Phải đọc: Tìm hiểu các hàm liên quan đến ngày đầy đủ trong Google Sheets.
Công thức Google Sheets tùy chỉnh để tính toán giờ làm bình thường / giờ làm thêm
Đây là công thức “đa năng” để tính số giờ giữa thời gian bắt đầu và thời gian kết thúc trong Google Sheets.
Dưới đây là hai câu hỏi phổ biến mà tôi dự kiến bạn sẽ đặt về công thức trên.
-
Điểm đặc biệt của công thức này là gì?
-
Tại sao bạn không chỉ đơn giản sử dụng <thời gian kết thúc – thời gian bắt đầu> để lấy sự khác biệt thời gian?
Đây là câu trả lời của tôi. Thời gian làm việc bắt đầu và kết thúc là vào hai ngày khác nhau.
Bạn có thể sử dụng phương pháp <thời gian kết thúc – thời gian bắt đầu> chỉ khi thời gian được ghi lại trong cùng một ngày hoặc thời gian nhập của bạn ở định dạng ngày / giờ (mốc thời gian).
Thời gian bắt đầu (trong ô A1): 10:10:18
Thời gian kết thúc (trong ô B1): 16:10:18
Ở đây, bạn có thể tuân theo công thức bình thường Google Spreadsheet như sau;
=B1-A1
Nhưng tôi nhiều lần đề nghị bạn sử dụng công thức Google Spreadsheet dưới đây sử dụng hàm MOD vì nó có thể tính toán chính xác sự khác biệt thời gian.
=mod(B1 - A1, 1)
Không cần phải nói, đây là một công thức tính thời gian tuyệt vời.
Bạn có thể tìm thấy thông tin chi tiết hơn ở đây – Google Sheets: Công thức tính giờ làm thêm tốt nhất.
Công thức Google Spreadsheet để tạo Danh sách Ngày đầy đủ trong một hàng / cột
Nhập năm (dưới dạng số) vào ô A1 và tháng (định dạng văn bản) vào ô A2.
Trong ô A2, nhập tháng dưới dạng văn bản có độ dài bằng ba ký tự.
Ví dụ: “Jan” cho tháng 1, “Feb” cho tháng 2 và vân vân. Các công thức còn lại sẽ giúp bạn.
Công thức:
=ArrayFormula(TRANSPOSE(TO_DATE(row(indirect(date($A$1,month($A$2&1),1)&":"&eomonth(date($A$1,month($A$2&1),1),))))))
Ví dụ:
Liên quan: Lấy danh sách đầy đủ tháng dựa trên một danh sách thả xuống trong Google Sheets.
Công thức SORTN Google Sheets để tìm Top N người giữ hạng
Bạn đã sử dụng chức năng SORTN trong Google Sheets? Nó thật là tuyệt vời!
Chức năng SORTN được sử dụng để sắp xếp dữ liệu của bạn. Nhưng có rất nhiều điều ẩn sau đằng sau.
Dưới đây là cách tìm các người giữ hạng Top N bằng một công thức SORTN.
Đối với thử nghiệm này, cột A của tôi chứa tên của người tham gia và cột B chứa điểm / số của họ.
=sortn(A2:B,3,0,2,FALSE)
Công thức này sẽ trả lại các người giữ hạng Top 3. Thay đổi số 3 thành số bạn muốn.
Ví dụ, thay đổi 3 thành 2 để nhận được hai người giữ hạng cao nhất.
Tương tự: Cách sử dụng Công thức SORTN trong Google Sheets để trích xuất N hàng đã sắp xếp
Công thức GoogleFinance để chuyển đổi Tiền tệ
Tôi muốn chuyển đổi đơn vị tiền tệ địa phương của mình thành Đô la Mỹ. Làm thế nào để làm điều đó? Câu trả lời là chức năng GoogleFinance.
Công thức Google Spreadsheet dưới đây sẽ cung cấp cho bạn tỷ giá USD sang INR. Điều đó có nghĩa là 1 USD = bao nhiêu INR?
=GoogleFinance("CURRENCY:USDINR")
Đối với các loại tiền tệ khác và chi tiết hơn, hãy làm theo hướng dẫn của tôi – Cách chuyển đổi tiền tệ trong Google Sheets bằng chức năng GoogleFinance.
Công thức truy vấn Google Sheets để nhóm / tóm tắt theo tháng / năm từ cột Ngày
Tất cả người dùng Bảng tính nên biết ít nhất một phương pháp để tạo tổng kết dữ liệu trong tay.
Dưới đây là một công thức tùy chỉnh giúp bạn tạo tổng kết cột ngày.
Sử dụng công thức dưới đây nếu Cột A chứa ngày và Cột B chứa số.
Công thức Google Sheets để làm Tóm tắt theo Tháng:
=query(A1:B8,"Select Month(A)+1,Sum(B) group by month(A)+1")
Công thức Google Sheets để làm Tóm tắt theo Năm:
=query(A1:B8,"Select Year(A),Sum(B) group by Year(A)")
Tìm hiểu chi tiết: Lọc theo Tháng và Năm trong Truy vấn trong Google Sheets.
Công thức truy vấn dựa trên công thức cho việc Tìm kiếm trong Google Spreadsheet bằng cách Sử dụng Vlookup
Thông thường, cột đầu tiên trong dữ liệu bảng tính của chúng ta có thể chứa ID sản phẩm, tên, mô tả, ngày, tên tháng vv.
Sử dụng công thức Vlookup, bạn có thể tìm kiếm trong cột đó và nếu tìm thấy khóa tìm kiếm, trả về một giá trị từ dòng tìm thấy.
Dưới đây là công thức Vlookup tùy chỉnh mà bạn có thể sử dụng.
=vlookup("March",A1:B13,2,FALSE)
Kết quả: 5,500.00
Trong đó “March” là khóa tìm kiếm.
A1:B13 là phạm vi để tìm kiếm.
Trong đó, công thức chỉ tìm kiếm khóa trong A1:A13, cột đầu tiên.
Công thức trả về giá trị từ cột thứ hai.
Hãy kiểm tra: Vlookup trong Google Sheets – 10 biến thể công thức, mẹo và thủ thuật.
Công thức IMPORTRANGE dựa trên công thức để kết nối hai Google Sheets
Làm thế nào để nhập nội dung từ Bảng tính A vào Bảng tính B? Câu trả lời là IMPORTRANGE.
Làm thế nào?
Chỉ cần sao chép URL của Bảng tính A và sử dụng nó trong công thức IMPORTRANGE trong Bảng tính B như sau.
=importrange("https://docs.google.com/spreadsheets/d/17Wh7kenRWRMZmbKGoZOVkNsX-3hM5eHuv-InZ02_2gc/edit#gid=1100774393","Sheet1!A1:B")
Phần cuối cùng của công thức là phạm vi dữ liệu để nhập. Trong phạm vi đó, Sheet1 là tên tab trong Bảng tính A và A1:B là phạm vi để nhập.
Bất kỳ thay đổi tương lai nào trong phạm vi Sheet1! A1: B sẽ được phản ánh trong Bảng tính B.
Hy vọng bạn sẽ thấy 10 công thức tùy chỉnh Google Spreadsheet trên hữu ích. Bất kỳ thắc mắc nào, hãy viết cho tôi trong phần bình luận.
[Crawlan.com](https://crawlan.com)
