Hỏi thêm về OFFSET()

[chibi]

Tôi đã tra cứu trên diễn đàn để tìm hiểu về hàm Offset(), nhưng chưa được thông lắm.
Nay nhờ các bạn giải thích (và cho ví dụ) rõ hơn về Offset().
Xin cảm ơn nhiều.

 

[nvson]

Hàm Offset:
* Công dụng:
Trả về tham chiếu đến một vùng nào đó được tính bằng một ô hoặc dãy ô bắt đầu và khoảng cách với số dòng, cột được chỉ định. Bạn có thể chỉ định số dòng, cột của vùng tham chiếu trả về.

* Cú pháp:
=OFFSET(reference, rows, cols, height, width)

Trong đó:
- reference: là vùng tham chiếu mà bạn muốn làm điểm xuất phát để tạo vùng tham chiếu mới. reference phải chỉ đến một ô hoặc một dãy ô liên tục, nếu không hàm sẽ trả về lỗi #VALUE!
- rows: là số dòng tính từ vùng xuất phát.
- cols: là số cột tính từ vùng xuất phát.
- height: là số dòng của vùng tham chiếu cần trả về. Bạn phải nhập số dương
- width: là số cột của vùng tham chiếu cần trả về. Bạn phải nhập số dương
* Lưu ý!
- Nếu các dòng và cột tham chiếu ngoài phạm vị trang bảng tính, OFFSET trả về lỗi #REF!
- Nếu height và width không nhập, mặc định nó giống như vùng tham chiếu xuất phát reference.

 

[ThuNghi]

Ở đây có ví dụ vận dụng offset và sumproduct
http://www.giaiphapexcel.com/forum/showthread.php?t=1638, #6

Và cũng có thể vận dụng offset trong vấn đề vlookup
Cụ thể, ta có bảng kê bán hàng gồm
MaKH........Ngay........TienBH
A01.................................
A02.................................
A0n.................................
A02.................................
A02.................................
A01.................................
......................................
Liệt kê TienBH của KH A02 sang sh khác, dùng hàm Vlookup, Bác khám phá thêm nhé.

 

[Shinec]

Nhân tiện có chibi lập topic này mình xin ké chút xíu.
To ThuNghi: Mình thấy bạn dùng offset(), index, match rất giỏi. mình ko cần dùng đến những hàm này nhưng muốn hiểu rõ về nó thêm, tài liệu mình có rồi nhưng hok có bài tập để áp dụng. TN có thể cho Shi một ít bài tập liên quan đến việc vận dụng những công thức nêu trên ko? càng nhìu càng tốt )
Thanks all!

 

[Pansy_flower]

Nhân tiện có chibi lập topic này mình xin ké chút xíu.
To ThuNghi: Mình thấy bạn dùng offset(), index, match rất giỏi. mình ko cần dùng đến những hàm này nhưng muốn hiểu rõ về nó thêm, tài liệu mình có rồi nhưng hok có bài tập để áp dụng. TN có thể cho Shi một ít bài tập liên quan đến việc vận dụng những công thức nêu trên ko? càng nhìu càng tốt )
Thanks all!

Anh coi các file kế toán hoặc các bài tập trong thư viện nha!.

 

[ThuNghi]

Cũng chả biết chỉ từ đâu, mà làm ví dụ thì có ý đó nhưng mà làm biếng quá, vậy Shi có làm bài tập gởi lên và mình sẽ chỉ cụ thể hơn để so sánh luôn.

 

[Shinec]

To TN & Secret:
Hông có gì hết, ngoài lý thuyết lấy trên mạng xuống nên hông thể hình dung hết những cái hay của những hàm này.
TN siêng tí đi =)) kiếm cho ít bài tập để Shi xem qua ko hiểu hỏi luôn cho tiện.
Trong thư viện có >20 bài tập nhưng không có liên quan đến những hàm này nên Shi mới đi xin nè.
Thanks all!

 

[anhtuan1066]

Một VD nhỏ cho bạn đây! Mở file VD.xls lên bạn sẽ thấy Sheet List có danh sách đễ dùng làm Validation cho sheet 2. Tôi dùng Define name có kết hợp với hàm Offset. Cái này có cái lợi là khi bạn thêm tên vào danh sách thì ở Sheet 2 sẽ tự động cập nhật thêm. Cách đặt tên củ là List1= =List!$A$1:$A$1000 Nó có 1 nhược điễm là Validation luôn luôn dư 1 vài dòng trắng ở dưới, còn dùng Offset thì ko. Ứng dụng của OFFSET thì rất nhiều, tùy vào sự sáng tạo của chúng ta, nhưng chủ yếu nó dc dùng cho mục đích tạo 1 mãng động: "Ở trường hợp này sẽ cho ra mãng này, ở trường hợp khác sẽ cho ra mãng khác..." Trong file ValidationMutilist.xls có áp dụng OFFSET đễ làm muti list đấy (Bạn qua Sheet DATA và gõ vào cột D những chử: F1, F2, F3, F4, F5... van van đễ thấy sự thay đỗi của Validation trong cột E nhé)
ANH TUẤN

 

[Mr Okebab]

Cảm ơn bạn về sự nhiệt tình nhé !!

Mình góp ý như sau :
Khi đặt name động thì ta nên đếm cả cột thay cho một vùng.
Bạn dùng :
=OFFSET(List!$A$1;0;0;COUNTA(List!$A$1:$A$1000);1)

Thì nên dùng
=OFFSET(List!$A$1;0;0;COUNTA(List!$A:$A);1)

Bình thường thì không sao, nhưng khi bạn xóa 1 hàng trong vùng đó, giả sửa bạn xóa 990 hàng thì vùng của bạn chỉ còn lại List!$A$1:$A$10
Như vậy công thức sẽ bị sai lệch.
Nếu bạn chọn cả cột thì sẽ tránh được điều này.

Thân!

 

[anhtuan1066]

Uh... rất hay! Tại vì tôi muốn làm cho 1 trường hợp đơn giản nhất thôi... Từ từ áp dụng sẽ phát hiện ra thêm tuyệt chiêu mà!
ANH TUẤN

 

[Shinec]

Cảm ơn AT nhìu nhìu nha . Để down về nghiên kú đã.
Còn 2 hàm index & match xin các bác cho em ít bài tập, khó khó chút càng tốt nha.

 

[anhtuan1066]

Thì bạn lấy file của bạn thanhtri này. Cả 1 đóng hàm INDEX và MATCH luôn

 

[ThuNghi]

Xin các Mod cho tôi "múa rìu" một chút.
Tôi có làm 1 file, dựa theo các bài đã có (thanhtri, anhtuan 1066...) tạo 1 ví dụ:
Ta có bảng kê xúât hàng hóa theo ngày. Vấn đề là liệt kê trong tháng i bảng kê xuất theo 1 KH bất kỳ và áp giá vào, giá tăng theo từng tháng. Vấn đề này có vẻ không thực tế nhưng để nghiên cứu hàm match, index, offset và sumproduct thì OK. Hy vọng giúp được bạn. Trong file nài o có macro.
Bạn có thể cắt ra từng phần mà vận dụng vào công việc.
Cũng nên tổng quát lại, do tôi đặt name lung tung.
Giải quyết vấn đề này nếu dùng filter, pivot hay VBA thì đơn giản hơn nhiều, nhưng đây là BT về match...

 

[vungoc]

File này rất hay, có thể vận dụng vào nhiều việc.
Cảm ơn bạn ThuNghi !

 

[Shinec]

TN có lòng thì tốt rồi, tuỳ theo mỗi người khi xem qua file này có thể áp dụng vào công việc của mình.