offset, match và +1, -1 !!!

[phongvutinh]

Bài này mình đã gửi trước đây rồi, và đã nhận được rất nhiều trả lời từ các bạn trên diễn đàn và mình cám ơn vì điều đó. Nay, nhìn lại kết quả trả lời thấy có một số chỗ không hiểu trong việc sử dụng hàm, mong các bạn giải thích dùm các công thức (khung được tô màu xanh), đặc biệt ở phần +1, -1 (phần tô đỏ) của công thức mang ý nghĩa gì và tại sao phải làm như vậy. Mong nhận được hồi âm của các bạn.

 

[MinhCong]

Bài này mình đã gửi trước đây rồi, và đã nhận được rất nhiều trả lời từ các bạn trên diễn đàn và mình cám ơn vì điều đó. Nay, nhìn lại kết quả trả lời thấy có một số chỗ không hiểu trong việc sử dụng hàm, mong các bạn giải thích dùm các công thức (khung được tô màu xanh), đặc biệt ở phần +1, -1 (phần tô đỏ) của công thức mang ý nghĩa gì và tại sao phải làm như vậy. Mong nhận được hồi âm của các bạn.

1/Giá trị -1
Bạn để ý hàm COUNTA(Sheet1!$A:$A)=31 -> (tính luôn chữ ngày ở đầu). Đem -1 chỉ còn 30 vì (kết quả đếm từ ô A4:A33 có 30 giá trị). Có thể thay COUNTA(Sheet1!$A:$A)-1=COUNTA(Sheet1!$A4:$A65536)

=OFFSET(Sheet1!$A$4,,,COUNTA(Sheet1!$A:$A)-1)

Kết quả sẽ là vùng từ A4:A33
2/Giá trị +1

=OFFSET(Ngay;;MATCH(Sheet1!$I5;Sheet1!$C$3:$F$3;0)+1)

MATCH(Sheet1!$I5,Sheet1!$C$3:$F$3,0)=> Sẽ có kết quả là 1 (vì tìm abc trong vùng C3:F3)
Mà offset(ngay....) => ngay là vùng A4:A33 -> Nếu lấy số cột về phía bên phải so với cột A4:A33 =MATCH(Sheet1!$I5,Sheet1!$C$3:$F$3,0)=1 thì nó chỉ mới tới cột B thôi. Do đó phải +1 mopứi tính đến cột cần tìm là C

Bài của Bạn có thể dùng công thức sau cho ô J5:

=SUMPRODUCT(($A$4:$A$65536>=$J$3)*($A$4:$A$65536<=$M$3)*($B$4:$B$65536=$J$2)*(INDIRECT(ADDRESS(4;MATCH($I5;$A$3:$F$3;0);1)&":"&ADDRESS(65536;MATCH($I5;$A$3:$F$3;0);1))))

- Hàm ADDRESS(4;MATCH($I5;$A$3:$F$3;0);1) => Có kết quả là $C$4
- Hàm ADDRESS(65536;MATCH($I5;$A$3:$F$3;0);1) => Có kết quả là $C$65536
=>INDIRECT(ADDRESS(4;MATCH($I5;$A$3:$F$3;0);1)&":"&ADDRESS(65536;MATCH($I5;$A$3:$F$3;0);1)) =$C$4:$C$65536

Hàm ADDRESS

Chức năng: Hàm ADDRESS trả về địa chỉ của một ô nào đó dưới dạng text dựa trên chỉ số dòng và cột.

Cú pháp hàm: ADDRESS(row_num,col_num,abs_num,a1,sheet_text)

- Row_num: là số thứ tự của dòng trong ô tham chiếu cần trả về.
- Col_num: là số thứ tự của cột trong ô tham chiếu cần trả về.
- Abs_num: là tùy chọn để xác định kiểu địa chỉ sẽ trả về. Abs_num có giá trị từ 1 đến 4:

• Abs_num = 1 hoặc 5 hoặc để trống: Kiểu địa chỉ trả về là tuyệt đối ($A$1).
• Abs_num = 2 hoặc 6: Kiểu địa chỉ trả về là dòng tuyệt đối và cột tương đối (A$1).
• Abs_num = 3 hoặc 7: Kiểu địa chỉ trả về là dòng tương đối và cột tuyệt đối ($A1).
• Abs_num = 4 hoặc 8: Kiểu địa chỉ trả về là tương đối (A1).

- A1: là tùy chọn để xác định dạng địa chỉ sẽ trả về. A1 có 2 giá trị là True or Fale:

• True hoặc để trống: dạng địa chỉ trả về là A1
• Fale: dạng địa chỉ trả về là R1C1

- Sheet_text: là tên sheet có trong địa chỉ tham chiếu cần trả về, mặc định là không có.

 

[phongvutinh]

Bạn MC này, vậy việc +1, -1 trong công thức có theo nguyên tắc mặc định nào không. Chẳng hạn công thức cần +1 hay -1 theo cột thì excel sẽ hiểu thế nào, tức là +1 hay -1 (cũng có thể là +2,-2...tùy mục đích đặt hàm) từ trên xuống hoặc từ phải sang trái và với dòng mình cũng có ý hỏi tương tự như cột vậy. Ví dụ như phần câu hỏi của mình bạn đã trả lời thì COUNTA(Sheet1!$A:$A)=31 -> (tính luôn chữ ngày ở đầu), Đem -1 chỉ còn 30 vì (kết quả đếm từ ô A4:A33 có 30 giá trị), như vậy có nghĩa là trừ ô đầu tiên cũng có thể hiểu là trừ từ trên xuống. Bạn hiểu ý mình hỏi không ?.

 

[MinhCong]

Bạn MC này, vậy việc +1, -1 trong công thức có theo nguyên tắc mặc định nào không. Chẳng hạn công thức cần +1 hay -1 theo cột thì excel sẽ hiểu thế nào, tức là +1 hay -1 (cũng có thể là +2,-2...tùy mục đích đặt hàm) từ trên xuống hoặc từ phải sang trái và với dòng mình cũng có ý hỏi tương tự như cột vậy. Ví dụ như phần câu hỏi của mình bạn đã trả lời thì COUNTA(Sheet1!$A:$A)=31 -> (tính luôn chữ ngày ở đầu), Đem -1 chỉ còn 30 vì (kết quả đếm từ ô A4:A33 có 30 giá trị), như vậy có nghĩa là trừ ô đầu tiên cũng có thể hiểu là trừ từ trên xuống. Bạn hiểu ý mình hỏi không ?.

Không có mặc định gì cả, đây chỉ là thuật toán suy luận thôi mà.
Bạn chỉ cần tính vùng của mình bắt đầu từ ô A4 trở xuống đúng không? Nhưng trong công thức ở trên lại tính từ ô A1 trở xuống (Vậy trừ đi 1 do phía trên ô A4 của Bạn khi dùng hàm Counta(A1:A3) có kết quả =1 nên phải trừ đi 1).
Nếu vùng dữ liệu của Bạn chỉ có từ A4:A33 (chỉ tính cho cột A thôi nhé) thì Bạn dùng hàm Counta(A4:A33) không có -1 (Nếu -1 sẽ bị thiếu mất 1 dòng)
Hàm offset(A4;;;counta(A4:A33)) có nghĩa là:
Bắt đầu từ ô A4 lấy vùng dữ liệu có số lượng dòng là counta(A4:A33).
Bạn đến đây xem về hàm offset nhé!
http://www.giaiphapexcel.com/forum/showthread.php?7188-H%C3%A0m-Offset-v%C3%A0-Validation