Thắc mắc công thức Offset (1 người xem)

[Phamanh1998]

Em chào anh chị, hiện nay em đang tìm đọc về sách sử dụng hàm OFFSET thì thấy trong ví dụ dưới đây em đang không hiểu phần giá trị bôi vàng tại sao lại trừ đi 1, em mong anh chị giải đáp giúp em ạ, em cảm ơn anh chị;

 

[befaint]

Tài liệu này ở đâu vậy mình? Chắc tác giả lúc viết bí quá.



Mình dùng hàm Lookup(), hoặc hàm Choose() nhé.

 

[Phamanh1998]

Tài liệu này ở đâu vậy mình? Chắc tác giả lúc viết bí quá.

View attachment 249710

Mình dùng hàm Lookup(), hoặc hàm Choose() nhé.

Em đang đọc trên Blog học exel online, kết quả trên dùng hàm INDEX+MATCH cũng ra kết quả tuy nhiên em đang học hàm OFFSET thì thấy tác giả viết như trên đang không hiêu sao chỗ em tô màu vàng phải trừ đi 1 ạ, mong anh giải đáp giúp em

 

[befaint]

Blog

Cái trang đó chuyên quảng cáo kiếm xèng thôi. Học tập mình tìm chỗ khác.

HÀM VLOOKUP

VLOOKUP Hàm VLOOKUP sẽ dò tìm một hàng (Row) chứa giá trị mà bạn cần tìm ở cột đầu tiên (bên trái) của một bảng dữ liệu (chữ V trong VLOOKUP có nghĩa là Vertical), nếu tìm thấy, nó sẽ tìm tiếp trong hàng này, và sẽ lấy giá trị ở cột mà bạn đã chỉ định trước. Cú pháp...

www.giaiphapexcel.com

Bài viết: Một cách khác VLOOKUP từ phải sang trái đơn giản hơn

Một cách khác VLOOKUP từ phải sang trái đơn giản hơn Nhân bài viết Một cách VLOOKUP từ phải sang trái trước, một số người đã phản hồi với kyo rằng hàm CHOOSE còn mới lạ và không được thân thuộc. Do vậy, nhân đây kyo xin giới thiệu cách kết hợp hàm VLOOKUP và hàm IF (cực kỳ quen thuộc) để giải...

www.giaiphapexcel.com

Sự khác nhau giữa hàm lookup và hàm vlookup, hlookup.

Chào mọi người trên diễn đàn Lookup có sự khác nhau không? Khi nào thì dùng lookup và sự hạn chế của hàm vlookup. Hlookup Cảm ơn mọi người nhiều!

www.giaiphapexcel.com

 

[Phamanh1998]

Vâng, em cảm ơn anh ạ, tại thấy tác giả viết chỗ đấy khó hiểu ạ

 

[befaint]

Vâng, em cảm ơn anh ạ, tại thấy tác giả viết chỗ đấy khó hiểu ạ

Thì đã bảo 'hắn' mải kiếm xèng rồi mà.
Lấy 1 giá trị cột A thì trong Offset theo 1 cell thuộc cột A là được rồi.
Lấy địa chỉ chỗ Match thì theo luôn cột B, mắc mệt gì Offset cả vùng dữ liệu.

 

[hml89]

Vâng, em cảm ơn anh ạ, tại thấy tác giả viết chỗ đấy khó hiểu ạ

Mình làm thử như này nó cũng ra kết quả này.

 

[kbw1]

Hàm này từ ngày bắt đầu vào diễn đàn mình mới biết,
Đến tận ngày hôm nay vẫn không hiểu được nó,
Vì mình xài hàm khác,

 

[VetMini]

Bài viết kia giải thích không đầy đủ.
Công thức ấy đặt trọng tâm vào vùng tham chiếu, cái mà nó gọi là "lookup table": $A$5:$B$11 (gồm 2 cột).
[theo tôi] công thức chỉ có giá trị thực tiễn khi vùng tham chiếu đã được cho vào một name để dễ tham chiếu.

Các công thức khác (từ bài #2 trở đi) dùng cột dò và cột kết quả cho nên giản dị hơn.

 

[tijikama]

Em đang đọc trên Blog học exel online, kết quả trên dùng hàm INDEX+MATCH cũng ra kết quả tuy nhiên em đang học hàm OFFSET thì thấy tác giả viết như trên đang không hiêu sao chỗ em tô màu vàng phải trừ đi 1 ạ, mong anh giải đáp giúp em

Trừ đi 1 là vì hàm offset là số cộng thêm, trong khi hàm match trả về số đến

Nếu bạn muốn học hàm thì cứ lên trang của microsoft mà đọc cho nguyên bản, rồi về mở 1 file excel ra tự thử là ngấm nhất
Như hàm offset, cấu trúc của nó là: OFFSET(reference, rows, cols, [height], [width])
Đọc cấu trúc có thể hơi khó hiểu 1 chút, nhưng tự thử thì sẽ thấy trong công thức này:
- Refer..: Là cell mà từ vị trí đó hàm offset sẽ dò tìm theo các giá trị đằng sau, refer.. có thể là 1 cell hoặc 1 mảng, đối với mảng thì kết quả sẽ dò tìm sẽ bắt đầu từ cell đầu tiên bên trái của mảng => như vậy chọn cell là được rồi, không cần chọn mảng. (hiểu nôm na cell mình chọn làm refer.. sẽ là hàng 0, cột 0)
- Rows: số hàng cách từ hàng chứa refer... , có thể là số dương hoặc số âm
Ví dụ, rows là 2 => sẽ lùi xuống 2 hàng từ hàng chứa refer.., (Refer..: là ô B3 => rows 2 sẽ lấy giá trị ở dòng 3+2 = 5 => B5)
rows là -1 => sẽ đi lên 1 hàng từ hàng chứa refer..: 3-1 = 2 => B2
- Cols: tương tự như rows, chỉ là chuyển thành cột, số dương là lùi sang phải, số âm là lùi sang trái
- height, width: là kích thước mảng mà bạn muốn trả về nếu cả 2 giá trị đều là 1 thì kết quả là 1 cell
Ví dụ:
=offset(B3,2,-1,2,3) sẽ trả về mảng từ A5:C6

 

[batman1]

Mình thấy mọi người cũng lạ. Có nhiều con đường đi tới kết quả, trong đó tác giả biết 1 trong các cách, đó là INDEX + MATCH. Người ta không bí về công thức. Người ta đang học về OFFSET và người ta chỉ không hiểu tại sao trong công thức kia lại có trừ đi 1: -1.

Đang học về OFFSET và không hiểu chứ không phải là muốn tìm công thức hay, ngắn gọn, pro, cao thủ, siêu nhân. Vậy tại sao cứ trách người ta là không dùng công thức khác? Khó hiểu.

Chủ thớt: thì bạn cứ thử xét không có -1 xem thế nào.
=OFFSET(A5:B9;MATCH(B1;OFFSET(A5:B9;0;1;ROWS(A5:B9);1);0)-1;0;1;1)

B1 = "Mar"

ROWS(A5:B9) = 5

OFFSET(A5:B9;0;1;ROWS(A5:B9);1) = OFFSET(A5:B9;0;1;5;1) = B5:B9
(vùng A5:B9 dịch sang phải 1 cột (được B5:C9), thu gọn thành vùng có 5 dòng và 1 cột, vậy kết quả là B5:B9)

MATCH(B1;OFFSET(A5:B9;0;1;ROWS(A5:B9);1);0) = MATCH(B1;B5:B9;0) = MATCH("Mar";B5:B9;0) = 3

OFFSET(A5:B9;MATCH(B1;OFFSET(A5:B9;0;1;ROWS(A5:B9);1);0);0;1;1) = OFFSET(A5:B9;3;0;1;1)
= A8 = $45
(vùng A5:B9 dịch xuống dưới 3 hàng (được A8:B12), thu gọn hành vùng có 1 dòng 1 cột tức thu gọn thành A8)

Rõ ràng kết quả A8 = $45 là sai. Nếu có thêm -1 thì:

OFFSET(A5:B9;MATCH(B1;OFFSET(A5:B9;0;1;ROWS(A5:B9);1);0)-1;0;1;1) = OFFSET(A5:B9;3-1;0;1;1)
= OFFSET(A5:B9;2;0;1;1) = A7 = $20
(vùng A5:B9 dịch xuống dưới 2 hàng (được A7:B11), thu gọn hành vùng có 1 dòng 1 cột tức thu gọn thành A7)
Kết quả bây giờ đúng.

Tóm lại nếu MATCH tìm thấy ở dòng k thì vùng chỉ được dịch xuống (k-1) dòng.

 

[phuongvq123]

Cái trang đó chuyên quảng cáo kiếm xèng thôi. Học tập mình tìm chỗ khác.

HÀM VLOOKUP

VLOOKUP Hàm VLOOKUP sẽ dò tìm một hàng (Row) chứa giá trị mà bạn cần tìm ở cột đầu tiên (bên trái) của một bảng dữ liệu (chữ V trong VLOOKUP có nghĩa là Vertical), nếu tìm thấy, nó sẽ tìm tiếp trong hàng này, và sẽ lấy giá trị ở cột mà bạn đã chỉ định trước. Cú pháp...

www.giaiphapexcel.com

Bài viết: Một cách khác VLOOKUP từ phải sang trái đơn giản hơn

Một cách khác VLOOKUP từ phải sang trái đơn giản hơn Nhân bài viết Một cách VLOOKUP từ phải sang trái trước, một số người đã phản hồi với kyo rằng hàm CHOOSE còn mới lạ và không được thân thuộc. Do vậy, nhân đây kyo xin giới thiệu cách kết hợp hàm VLOOKUP và hàm IF (cực kỳ quen thuộc) để giải...

www.giaiphapexcel.com

Sự khác nhau giữa hàm lookup và hàm vlookup, hlookup.

Chào mọi người trên diễn đàn Lookup có sự khác nhau không? Khi nào thì dùng lookup và sự hạn chế của hàm vlookup. Hlookup Cảm ơn mọi người nhiều!

www.giaiphapexcel.com

Em cũng đến excel bắt đầu từ trang đó bác à, trước khi e biết diễn đàn này, e toàn học ở đó, cơ bản đến nâng cao, có nhiều chủ đề và công thức khá hay đó bác, tất nhien là trang đó cũng có bán khóa học nhưng cơ bản là nếu ai là người mới thì e khuyên học từ đó vì em thấy trang đó giải thích công thức cơ bản rất chi tiết và dễ hiểu đó

 

[befaint]

Em cũng đến excel bắt đầu từ trang đó

Bởi vì họ đã chi nhiều tiền quảng cáo, quăng sẵn cái lưới to đùng chờ sẵn những người gõ từ khoá liên quan là sa lưới.
99% kết quả bạn tìm đều thấy họ hiện ra trên đầu mà. Cái đó là tiền cả đấy.

Còn về nội dung, công thức hay các kiểu... thì miễn bàn nhé. Nếu bạn chịu khó tìm, và tìm được ở diễn đàn này thì bạn sẽ thay đổi nhận định trên.

 

[phuongvq123]

Bởi vì họ đã chi nhiều tiền quảng cáo, quăng sẵn cái lưới to đùng chờ sẵn những người gõ từ khoá liên quan là sa lưới.
99% kết quả bạn tìm đều thấy họ hiện ra trên đầu mà. Cái đó là tiền cả đấy.

Còn về nội dung, công thức hay các kiểu... thì miễn bàn nhé. Nếu bạn chịu khó tìm, và tìm được ở diễn đàn này thì bạn sẽ thay đổi nhận định trên.

) quảng cáo bây giờ là thời đại, sao tránh được, tìm đâu mấy trang như diễn đàn mình đâu ạ

 

[ptm0412]

Em cũng đến excel bắt đầu từ trang đó bác à, trước khi e biết diễn đàn này, e toàn học ở đó, cơ bản đến nâng cao, có nhiều chủ đề và công thức khá hay đó bác, tất nhien là trang đó cũng có bán khóa học nhưng cơ bản là nếu ai là người mới thì e khuyên học từ đó vì em thấy trang đó giải thích công thức cơ bản rất chi tiết và dễ hiểu đó

Trang GPE cũng có chủ đề giải thích tất tần tật hàm trong Excel: Tra cứu tất cả hàm của Excel viết từ 2008 (tác giả @BNTT)
Còn có các chủ đề khác dịch từ tiếng Anh chẳng hạn như dịch từ cuốn Formulas and Functions with Microsoft Office Excel 2007 của Paul McFedries (cũng BNTT năm 2018)
Chủ đề Mỗi ngày 1 tuyệt chiêu 3 người dịch 2008
Và rất nhiều chủ đề cơ bản khác. Chỉ cần đọc link dưới chữ ký của BNTT là đã có cả ngàn thứ hay ho hơn cái trang kia, thậm chí nhiều trang trong đó có trang kia phải vào GPE cóp nhặt. Cụ thể là khi chủ đề mỗi ngày 1 tuyệt chiêu được viết hàng ngày, trên 100 trang web và diễn đàn khác copy về làm của mình.

 

[VetMini]

Mình thấy mọi người cũng lạ. Có nhiều con đường đi tới kết quả, trong đó tác giả biết 1 trong các cách, đó là INDEX + MATCH. Người ta không bí về công thức. Người ta đang học về OFFSET và người ta chỉ không hiểu tại sao trong công thức kia lại có trừ đi 1: -1.

Đang học về OFFSET và không hiểu chứ không phải là muốn tìm công thức hay, ngắn gọn, pro, cao thủ, siêu nhân. Vậy tại sao cứ trách người ta là không dùng công thức khác? Khó hiểu.
...

Tôi thì chỉ tiếc thớt chép ở đâu đấy mà không nói rõ ra.
Rất có thể nơi ấy người ta có giải thích tại sao dùng OFFSET mà không dùng INDEX hay cái gì đó. Hoặc như mấy cái diễn đàn khác, người đưa công thức tương tự lên có cảnh báo về tính chất "volatile" của hàm này.
Bài #1 của thớt không có chứng của nguồn nên chịu thua chỗ này.

Điểm tiếc thứ hai là bài viết mà thớt quote có hơi cẩu thả. Dòng trên công thức chiếu tới vùng A5:B9. Dòng kế tiếp vùng tham chiếu là A5:B11. Tuy nó không ảnh hưởng gì đến kết quả hay câu hỏi của thớt nhưng vẫn có khả năng gây hoang mang cho vài người, trong số ấy có tôi.

... kết quả trên dùng hàm INDEX+MATCH cũng ra kết quả tuy nhiên em đang học hàm OFFSET thì thấy tác giả viết như trên đang không hiêu sao chỗ em tô màu vàng phải trừ đi 1 ạ, mong anh giải đáp giúp em

Bạn đã biết hàm INDEX cho nên tôi giải thích theo cách so sánh 2 hàm.

So sánh OFFSET và INDEX:
OFFSET dịch ra tiếng Việt là lệch (vị trí tương đối). Lệch 5 có nghĩa là cách vị trí ban đầu 5 vị trí => tức là 6.
INDEX dịch ra tiếng Việt là chỉ số (vị trí tuyệt đối). Phần tử 5 trong mảng là phần tử đứng ở vị trí thứ 5.

MATCH trả về một vị trí tuyệt đối cho nên:
- Khi dùng hàm INDEX thì dùng thẳng luôn. Nếu Match trả về 5 thì chỉ số 5 là đúng phần tử thứ 5.
- Khi dùng hàm OFFSET thì phải thêm bài toán để chuyển vị trí tuyệt đối (của match) sang tương đối. Con số -1 kia chính là bài toán chuyển. Nếu Match trả về 5 thì lệch 5 sẽ là phần tử thứ 6 (kết quả trật chỗ). Đem 5 trừ đi 1 thì lệch 4 sẽ là phần tử thứ 5 (đúng).

 

[Phamanh1998]

Dạ em cảm ơn các anh chị đã gỡ rối cho em chủ đề trên ạ