Anh chị giúp đỡ em về công thức mảng động của hàm OFFSET

[longtay1111]

Em kính chào các anh chị trong nhóm GIaiphapexcel

Nhân dịp đầu năm 2022 em xin kính chúc toàn thể anh chị em trong nhóm Giaiphapexcel một năm mới dồi dào sức khỏe, tài lộc vẹn toàn, gia đình sung túc và hạnh phúc đong đầy.

Em có thắc mắc muốn nhờ các anh chị giải đáp giúp em với ạ, em có viết tiêu đề nhưng chưa sát với bài của em, rất mong được anh chị thông cảm.

Em đang tìm doanh số bán hàng lớn nhất và nhỏ nhất của 1 nhân viên bán hàng( doanh số nhỏ nhất với điều kiện >0 ).

Em đang phải viết thủ công từng dòng

K3=AGGREGATE(15,6,OFFSET($B$4:$B$34,0,1,31,1)/(OFFSET($B$4:$B$34,0,1,31,1)>0),1)
K4=AGGREGATE(15,6,OFFSET($B$4:$B$34,0,2,31,1)/(OFFSET($B$4:$B$34,0,2,31,1)>0),1)
....
Em thấy công thức trên có quy luật do vậy em thay thế bằng công thức

K3= AGGREGATE(15,6,OFFSET($B$4:$B$34,0,ROW(A1),31,1)/(OFFSET($B$4:$B$34,0,ROW(A1),31,1)>0),1)- em viết công thức này để kéo từ trên xuống để không phải viết thủ công từng dòng như trên, nhưng là do đặc thù công thức thì kéo theo từng dòng nhưng dữ liệu lại lấy theo cột, và em đã viết công thức thay thế
K3= AGGREGATE(15,6,OFFSET($B$4:$B$34,0,ROW(A1),31,1)/(OFFSET($B$4:$B$34,0,ROW(A1),31,1)>0),1) em thấy toàn báo lỗi

Nhưng khi thay thủ công thức trên ROW(A1)=1, ROW(A2)=2…thì công thức trên lại trả ra kết quả đúng( như cách viết thủ công ban đầu )

Em nhờ anh chị trong nhóm giúp em chỉnh sửa lại công thức trên cho hợp lý, chỉ cần viết 1 lần để kéo từ trên xuống, hiện tại em đang viết rất thủ công từng dòng 1 nên mất rất nhiều thời gian ạ.

Em gửi file đính kèm, anh chị vui lòng check mail giúp em.

Em xin trân trọng cảm ơn anh chị rất nhiều.

 

[Hoàng Tuấn 868]

chỉ cần viết 1 lần để kéo từ trên xuống,

Bạn thử tham khảo nhé.

 

[NguyenHoangDiep]

Cách thủ công đơn giản thì bạn thêm cột thứ tự nhân viên như mình rồi sửa công thức là kéo ok.

 

[VetMini]

Mảng bài này đâu có thực sự "động". Dùng Index chứ ai lại dùng Offset.

(mà xét >0 chi vậy? Doanh thu cũng có số âm sao?)

 

[longtay1111]

Bạn thử tham khảo nhé.

Thật tuyệt vời luôn ạ, có thế mà em không nghĩ ra,đúng là gặp thầy Excel có khác.

Bác nhiệt tình với em quá ạ, em gửi bài muộn thế mà bác vẫn trả lời em, em cảm ơn bác nhiều nhiều ạ.

Đầu xuân năm mới em xin chúc bác và Gia đình 1 năm mới sức khoẻ dồi dào, an khang thịnh vượng, một lần nữa em chân thành cảm ơn bác.

Chúc bác 1 ngày mơi tốt lành và nhiều niềm vui.

Trân trọng

Bạn thử tham khảo nhé.

Bài đã được tự động gộp: 8/2/22

Cách thủ công đơn giản thì bạn thêm cột thứ tự nhân viên như mình rồi sửa công thức là kéo ok.

Dạ đúng rồi ạ, tận dụng luôn cột STT để lấy dữ liệu. Một cách làm rất hay và sáng tạo. Em trân trọng cảm ơn bác nhiều ạ.

Chúc bác 1 ngày mới tốt lành ạ.

Trân trọng

Bài đã được tự động gộp: 8/2/22

Mảng bài này đâu có thực sự "động". Dùng Index chứ ai lại dùng Offset.

(mà xét >0 chi vậy? Doanh thu cũng có số âm sao?)

Dạ em phải lấy lớn hơn không để biết giá trị nhỏ nhất là bao nhiêu bác ạ( khác 0 ), còn những giá trị = 0( có nghĩa là có ngày không bán được hàng em sẽ thống kê trong danh mục khác bác ạ 0.

Em chân thành cảm ơn bác nhiều nhiều, bác Hoàng Tuấn 868 đã hướng dẫn em làm theo cách của bác ( index ) và em đã làm được rồi ạ.

Em thật khâm phục trình độ của các bác trong nhóm, em thấy bài nào các bác cũng có cách giải quyết và nhiều phương án làm rất hay và sáng tạo. Không hiểu trình độ của em phải học 50 năm nữa mới theo được trình độ các bác.

Bác VetMini cũng đã nhiều lần trả lời bài và hướng dẫn excel cho em, một lần nữa em chân thành cảm ơn bác rất nhiều. Năm mới em chúc và và gia đình 1 năm mới sức khoẻ dồi dào, an khang thịnh vượng và đạt được nhiều thành công trong cuộc sống.

Em xin trân trọng cảm ơn ạ.

 

[Hoàng Tuấn 868]

tận dụng luôn cột STT để lấy dữ liệu.

Trường hợp này phải cẩn thận, nếu không đúng thứ tự các cột thì tèo, bài #2 là excel tự tìm cột theo mã nên không nhầm được.

 

[huhumalu]

Sẵn có bài này thì cho em hỏi tiếp luôn.
Phần max thì em làm theo công thức bên dưới.

=MAX(($I3=$C$3:$F$3)*($C$4:$F$34))

Nhưng min nếu làm tương tự thì dĩ nhiên trả về 0
Nếu theo yêu cầu là trả về số >0 nhỏ nhất thì có thể làm theo cách này không và chỉnh như thế nào.

 

[VetMini]

...
Dạ đúng rồi ạ, tận dụng luôn cột STT để lấy dữ liệu. Một cách làm rất hay và sáng tạo. Em trân trọng cảm ơn bác nhiều ạ.

Kết luận sai lầm.
Dùng số thứ tự để thay mã là cách làm rất nguy hiểm đối với Excel. Nên tránh dùng.
Chỉ có các phầm mềm Database mới dùng số thứ tự (điển hình dưới dạng automunber). Nhưng số này là do chính Database quản lý và kiểm soát. Người dùng không chỉnh sửa được.

...
Dạ em phải lấy lớn hơn không để biết giá trị nhỏ nhất là bao nhiêu bác ạ( khác 0 ), còn những giá trị = 0( có nghĩa là có ngày không bán được hàng em sẽ thống kê trong danh mục khác bác ạ 0.
...

MAX:
Đương nhiên số 0 là số nhỏ nhất trong nhóm. Không cần phải loại chúng ra.

MIN:
Chỉ số 15 theo hàm Aggregate là so sánh với hàm SMALL chứ không phải hàm MIN.
Cái tham số cuối cùng của Aggregate là tham số cho biết hạng thứ.
Đếm số 0's trong mảng, cộng thêm 1.

Sẵn có bài này thì cho em hỏi tiếp luôn.
Phần max thì em làm theo công thức bên dưới.

=MAX(($I3=$C$3:$F$3)*($C$4:$F$34))

Nhưng min nếu làm tương tự thì dĩ nhiên trả về 0
Nếu theo yêu cầu là trả về số >0 nhỏ nhất thì có thể làm theo cách này không và chỉnh như thế nào.

MIN không thể loại 0's.
Có 3 cách:
1. Dùng hàm Aggregate. Cái mẹo của Aggregate là dùng con toán chia để tạo lỗi #DIV0!. Aggregate có khả năng bỏ qua các trị lỗi.
2. Dùng hàm Small. Theo chỉ dẫn trên, đếm số 0's trong mảng. Cái mẹo ở đây là trị nhỏ nhất sẽ đứng thứ n+1, sau n trị 0's
3. Dùng hàm Dmin. Hàm database cho phép lọc theo nhiều điều kiện.

 

[huhumalu]

Dạ, cảm ơn anh.
Để em tìm hiểu theo các cách trên.

 

[longtay1111]

Kết luận sai lầm.
Dùng số thứ tự để thay mã là cách làm rất nguy hiểm đối với Excel. Nên tránh dùng.
Chỉ có các phầm mềm Database mới dùng số thứ tự (điển hình dưới dạng automunber). Nhưng số này là do chính Database quản lý và kiểm soát. Người dùng không chỉnh sửa được.


MAX:
Đương nhiên số 0 là số nhỏ nhất trong nhóm. Không cần phải loại chúng ra.

MIN:
Chỉ số 15 theo hàm Aggregate là so sánh với hàm SMALL chứ không phải hàm MIN.
Cái tham số cuối cùng của Aggregate là tham số cho biết hạng thứ.
Đếm số 0's trong mảng, cộng thêm 1.


MIN không thể loại 0's.
Có 3 cách:
1. Dùng hàm Aggregate. Cái mẹo của Aggregate là dùng con toán chia để tạo lỗi #DIV0!. Aggregate có khả năng bỏ qua các trị lỗi.
2. Dùng hàm Small. Theo chỉ dẫn trên, đếm số 0's trong mảng. Cái mẹo ở đây là trị nhỏ nhất sẽ đứng thứ n+1, sau n trị 0's
3. Dùng hàm Dmin. Hàm database cho phép lọc theo nhiều điều kiện.

Bác dạy quá chuẩn luôn ạ, có lần em cùng đã dùng cách đếm số 0 rồi cộng thêm 1 để lấy giá trị Min >0, giờ bác nhắc em mới nhớ ra ạ.

Còn cách dùng số STT em sẽ lưu ý như nhắc nhở của bác.

Em cảm ơn bác nhiều nhiều, nhờ sự giúp đỡ hỗ trợ của bác em đã hiểu thêm rất nhiều rồi ạ.

Hy vọng trong thời gian tới có những vấn đề thắc mắc chưa rõ em hy vọng anh chị em trong nhóm nói chung và bác nói riêng hỗ trợ em vơi ạ.

Một lần nữa em chân thành cảm ơn các anh chị em trong nhóm Giaphapexcel nói chung và các bác VetMini,Hoàng Tuấn 868 đã giúp đỡ em rất nhiệt tình không những riêng bài này mà cả những bài khác trước đó em có hỏi ạ.

Trân trọng