Hàm offset là giữa những hàm tham chiếu rất hay được dùng trong Excel. Đặc biệt là Kế toán excel.
Bạn đang xem: Hàm offset và match trong excel
Khi chúng ta biết áp dụng hàm offset để tùy chỉnh thiết lập các sổ kế toán thì tệp tin kế toán excel của các các bạn sẽ trở bắt buộc gọn và nhẹ hơn rất nhiều. Thậm chí còn nó triển khai được một số công dụng giống như ứng dụng kế toán trả phí.
Trong nội dung bài viết này bọn họ sẽ đi kiếm hiểu việc ứng dụng hàm offset để kiếm được số chi phí thuế GTGT cho phiếu thu với phiếu chi.
1. Ứng dụng hàm offset phối kết hợp hàm match trong kế toán excel
Ở phiếu bỏ ra số PC01/001 là nghiệp vụ giao dịch thanh toán tiền cước viễn thông T12/07. Khi ấy nghiệp vụ họ hạch toán đang là:
Nợ 6422 : 150.774
Nợ 1331 : 15.101
Có 1111 : 168.875
Chúng ta nên thể hiện tại được bên trên phiếu chi nghiệp vụ tài chính phát sinh trên. Bởi lẽ khi thanh toán bọn họ phải giao dịch thanh toán tiền cho bên viễn thông theo Tổng giá bán thanh toán. (Giá bao gồm thuế VAT)
Tuy nhiên mặt Sheet Nhập phạt sinh, họ có 2 cái nghiệp vụ:
1. PC01/001 – Nợ 6422/ có 1111: 150.774
2. PC01/001 – Nợ 1331/ bao gồm 1111: 15.101
Đối với việc lấy giá trị tiền của cây viết toán thứ nhất rất dễ.
Bởi lẽ chỉ việc dùng hàm vlookup với điều kiện tìm kiếm chính là số phiếu chi: PC01/001 là hoàn toàn có thể tìm được rồi.
Tuy nhiên khi sử dụng hàm vlookup thì chỉ tìm được giá trị chi phí của dòng nghiệp vụ đầu tiên của số phiếu đưa ra đó. Tức là dùng hàm vlookup chỉ kiếm được giá trị nghiệp vụ 1 và không tìm kiếm được cực hiếm của nhiệm vụ 2.
Khi đó họ sẽ đề xuất dùng hàm offset kết hợp với hàm match để tìm cực hiếm của nhiệm vụ 2.
Lưu ý: Còn có nhiều cách khác cùng đây chỉ là một trong những trong những cách
Các bạn download file mẫu mã về phân tích cách sử dụng hàm offset phối kết hợp hàm match
Bên cạnh đó, gồm những nhiệm vụ chi tiền nhưng mà không vạc sinh nhiệm vụ thuế GTGT, ví như trường phù hợp dưới, khi chi tiền lâm thời ứng thì chỉ tạo nên một nhiệm vụ duy nhất:
PC01/002 – N141/ C1111: 15.101
2. Link tải về file mẫu
Các bạn hãy tải về bài mẫu dưới đây để hiểu rõ hơn về câu hỏi làm cầm cố nào lấy phần thuế GTGT của một phiếu đưa ra nào đó bởi các áp dụng hàm Offset kết phù hợp với hàm Match.
Sau kia xem lại cách tính 1331 sinh hoạt phiếu bỏ ra của file Phiếu Thu – Chi auto đã bao gồm trên trang của bọn chúng ta.
Trong quá trình nghiên cứu, nếu chạm chán khó khăn gì bạn đừng ngại contact với ad qua mail nhé. (Webkynang.vn
Trân trọng cảm ơn sự góp ý của 2 chúng ta để bài viết trở nên hoàn thành xong hơn.
phuongthao.ng.0303DuongnxTrường muốn nhận được nhiều góp ý hơn từ chúng ta độc trả và đặc biệt là 2 bạn.
Có thể các bạn đã biết phương pháp sử dụng hàm OFFSET. Nhưng chúng ta đã biết phối kết hợp hàm OFFSET cùng với hàm MATCH nhằm tìm kiếm theo khá nhiều điều kiện đồng thời chưa? Hãy cùng bọn chúng mình tò mò trong bài bác tập Excel dưới đây nhé.
Lý thuyết về hàm OFFSET với hàm MATCH
Hàm OFFSET
Hàm OFFSET trong Excel được thực hiện khi bọn họ cần coi hoặc giám sát dữ liệu của một ô hoặc một dãy các ô trong bảng tính phụ thuộc vào một vùng tham chiếu bao gồm sẵn.
Cú pháp của hàm OFFSET có dạng như sau:
=OFFSET(reference, rows, cols, height, width)
Trong đó:
Reference: là vùng tham chiếu làm cơ sở cho hàm (làm điểm xuất phát) để sản xuất vùng tham chiếu mới.Rows: là số dòng bên trên hoặc bên dưới reference, tính từ ô thứ nhất (ô ở góc cạnh trên bên trái) của reference.Cols: là số cột phía trái hoặc bên buộc phải reference, tính tự ô thứ nhất (ô ở góc trên mặt trái) của reference.Height: là số cái của vùng tham chiếu đề nghị trả về. Height đề nghị là số dương.Width: là số cột của vùng tham chiếu buộc phải trả về. Width đề nghị là số dương.Hàm MATCH
Hàm MATCH vào Excel là 1 trong những hàm tìm kiếm kiếm có tác dụng tìm một mục được hướng dẫn và chỉ định trong phạm vi của ô, kế tiếp trả về vị trí kha khá của mục kia trong phạm vi này.
Cú pháp của hàm này còn có dạng như sau:
Trong đó bao hàm các nguyên tố là:
lookup_value: giá bán trị buộc phải tìm (tìm tìm theo cực hiếm nào?). Giá trị này có thể là số, text, xúc tích hoặc tham chiếu ô.lookup_array: Vùng search kiếm (tìm kiếm ở đâu?). Bạn cũng có thể dò kiếm tìm trên 1 mảng (array) hoặc 1 vùng ô (range).match_type: Phương pháp search kiếm (tham số ko bắt buộc). Có 3 cách thức tìm kiếm:Không nhấp tham số hoặc nhập "1": kiếm tìm kiếm tương đối theo giá bán trị nhỏ hơn ngay sát nhất.Nhập "-1": tìm kiếm kha khá theo giá trị lớn hơn gần nhất. Chú ý: vùng kiếm tìm kiếm rất cần được sắp xếp theo sản phẩm tự giảm dần, nghĩa là, từ giá bán trị lớn nhất đến giá trị nhỏ nhất hoặc trường đoản cú Z cho tới A.Nhập số 0: Tìm đúng chuẩn theo quý hiếm lookup_value mà lại nó search được.Vậy khi bọn họ kết hợp hàm MATCH và hàm OFFSET sẽ với lại công dụng như nạm nào? Hãy thuộc thử mức độ với bài xích tập ở ngay dưới đây để biết nhé.
Bài tập phối hợp hàm OFFSET với hàm MATCH
Đề bài
Cho 2 bảng dữ liệu như sau:
Yêu cầu: xác minh đơn giá cho từng món đồ được lựa chọn trong bảng giá dựa theo model ở cột B và kích thước sản phẩm sinh hoạt cột C. Trong đó model và kích cỡ sản phẩm được xác định phụ thuộc bảng danh mục hàng hóa.
Cách thực hiện
Bước 1: Nhập công thức hàm OFFSET vào ô D6 (Cột đơn giá) trong bảng báo giá.
Bước 2: đưa sang bảng danh mục hàng hóa, chọn ô A3 làm cho gốc tọa độ. Bọn họ nên lựa chọn vị trí thứ nhất trong bảng làm cho gốc là tốt nhất. Các bạn nhớ thắt chặt và cố định tọa độ của ô A3 để khi đến bước sau hoàn toàn có thể copy công thức xuống loại khác nhé.
Bước 3: Lồng hàm MATCH vào cách làm để tìm kiếm số mẫu sẽ được dịch rời (rows trong hàm OFFSET), đem lookup_value là ô B6 của bảng báo giá. Sau đó chúng ta tiếp tục đưa sang bảng hạng mục hàng hóa với nhập tham chiếu mã sản phẩm là tự ô A4:A19.
Đừng quên cố định tọa độ mang đến vùng tham chiếu A4:A19 nhé:
Bởi vì model của bọn họ có format là Text đề nghị tham số match_type bọn họ sẽ nhập là số 0 để tìm kiếm chính xác. Sau đó chúng ta đóng hàm MATCH lại.
Bước 4: Lồng hàm MATCH vào công thức để tra cứu số cột đã được di chuyển (cols vào hàm OFFSET). Bọn họ sẽ đem lookup_value mang đến hàm MATCH là ô C6 vào bảng báo giá. Sau đó các bạn chuyển sang trọng bảng danh mục hàng hóa, lựa chọn vùng tham chiếu là B3:E3.
Đừng quên cố định tọa độ mang lại vùng tham chiếu và nhập match_type là 0 để tìm kiếm đúng đắn nhé.
Bước 5: Thêm dấu ngoặc nhằm đóng công thức hàm lại với bấm Enter để thu được kết quả.
Các bạn đối chiếu kết quả họ tính được ở bảng báo giá với bảng hạng mục hàng hóa thuở đầu sẽ thấy trên đây là công dụng chính xác. Sau đó chúng ta sao chép bí quyết xuống những dòng bên dưới là được nhé.
Bước 6: Đây là 1 trong bước tùy chọn. Các bạn có thể lựa chọn làm hoặc ko làm. Ví như trong báo giá của bạn có loại trống thì khi coppy công thức xuống sẽ hiển thị lỗi #N/A như vậy này.
Xem thêm: Cách Đổi Inch Sang Cm Trong Excel 2010, 2003, Cách Đổi Inch Sang Cm Trong Word, Excel
Chúng ta chỉ việc thêm một biện luận vào công thức hàm OFFSET ở bước 5 để nếu chạm mặt ô trống, không tồn tại giá trị model hay kích thước thì ko hiển thị lỗi #N/A. Chắc hẳn chúng ta cũng sẽ biết là chúng ta nên lồng hàm IF và hàm OR vào đây. Công thức cụ thể như sau:
Tất nhiên, khi lồng thêm hàm thì các bạn cần thêm một vệt ngoặc solo ở cuối cách làm thì mới tiến hành được. Công thức cuối cùng có dạng như sau:
Kết trái mà chúng ta thu được như sau:
Kết luận
Qua bài viết này các chúng ta cũng có thể thấy rằng chỉ cần phải biết cách phối kết hợp các hàm không còn xa lạ là chúng ta có thể xử lý được một vấn đề phức tạp.