Xin giúp đỡ - Hàm trích nhiều thông tin từ 1 mã ô, mã hàng,...không dùng lệnh filter (1 người xem)

[acthụavert]

Xin chào cả nhà,

Em là dân nghiệp dư, học nông nghiệp cơ mà cũng phải làm nhiều về excel, đang bị vướng một cái nhức đầu mà tầm hiểu biết của em chưa đến nơi, xin phép post lên mong cả nhà giúp đỡ ạ!
Chẳng là bên em có nhiều lệnh sản xuất, nhiều cây trồng trồng trên 1 lô thửa nào đó.

Ví dụ như: Xà lách, cà chua, dưa leo, bầu bí, súp lơ đều trồng trên lô 4.1
Nếu em filter lô 4.1 thì OK nó hiện ra đầy đủ các cây trồng trên.
Tuy nhiên, nếu em đặt số 4.1 theo một ô nào đó, kéo xuống 5 ô cho giống nhau.
Khi đó, nếu em dùng vlookup dò giá trị 4.1 thì nó chỉ lấy cái đầu tiên (như ví dụ trên là xà lách, các cây còn lại nó không hiểu)

Em muốn hỏi cả nhà, có cách nào để liệt kê các sản phẩm vào ô (dùng bằng hàm) mà không cần dùng filter hay không ạ!

Cụ thể em có cái file ví dụ đính kèm, sheet 1 là danh sách lệnh và cây trồng, sheet 2 em muốn liệt kê ra trên lô thửa đó có những cây nào theo thứ tự và hiện ra hết; có bác nào biết giúp em tí ạ!

Em xin cam ơn!

 

[befaint]

Xin chào cả nhà,

Em là dân nghiệp dư, học nông nghiệp cơ mà cũng phải làm nhiều về excel, đang bị vướng một cái nhức đầu mà tầm hiểu biết của em chưa đến nơi, xin phép post lên mong cả nhà giúp đỡ ạ!
Chẳng là bên em có nhiều lệnh sản xuất, nhiều cây trồng trồng trên 1 lô thửa nào đó.

Ví dụ như: Xà lách, cà chua, dưa leo, bầu bí, súp lơ đều trồng trên lô 4.1
Nếu em filter lô 4.1 thì OK nó hiện ra đầy đủ các cây trồng trên.
Tuy nhiên, nếu em đặt số 4.1 theo một ô nào đó, kéo xuống 5 ô cho giống nhau.
Khi đó, nếu em dùng vlookup dò giá trị 4.1 thì nó chỉ lấy cái đầu tiên (như ví dụ trên là xà lách, các cây còn lại nó không hiểu)

Em muốn hỏi cả nhà, có cách nào để liệt kê các sản phẩm vào ô (dùng bằng hàm) mà không cần dùng filter hay không ạ!

Cụ thể em có cái file ví dụ đính kèm, sheet 1 là danh sách lệnh và cây trồng, sheet 2 em muốn liệt kê ra trên lô thửa đó có những cây nào theo thứ tự và hiện ra hết; có bác nào biết giúp em tí ạ!

Em xin cam ơn!

- Điều kiện "Từ ngày, đến ngày" có dùng đến không?
- Không dùng hàm mà dùng vba được không?

 

[acthụavert]

- Điều kiện "Từ ngày, đến ngày" có dùng đến không?
- Không dùng hàm mà dùng vba được không?

Bác tinh quá ạ
Điều kiện đó em có dùng, cụ thể là yêu cầu tưới được thực hiện hàng tuần. Ví dụ cây trồng đó đã thu hoạch thì sẽ bị loại, không còn hiện trong list các cây trồng trên lô thửa. Mà vì phức tạp quá nên em không dám hỏi, nếu giải quyết được cái hàm kìa thì em tự đưa điều kiện vào là được
Nếu dùng VBA cũng được ạ. Nhưng nói thật là bên em tùy biến và thay đổi rất nhiều, VBA thì em không rành lắm, chỉ sợ các bác tư vấn xong vài bữa em lại chả biết tùy chỉnh ạ!

 

[nguyen6571gpex]

Bạn có thể tham khảo thêm bài này!
http://www.giaiphapexcel.com/diendan/threads/xin-giúp-về-hàm-vlookup.124500/

 

[bomberman211]

Thực ra với yêu cầu của bạn dùng VBA hoặc Pivot table sẽ tiện hơn rất nhiều.
Cơ mà mình cũng làm lại file cho bạn chỉ sử dụng Formula (có thêm 1 cột phụ đầu mỗi sheet), yêu cầu là bạn phải Sort lại bảng trong sheet 1 như hình.

 

[befaint]

Sort lại bảng trong sheet 1

Cái này là có vấn đề gì gì á...

 

[bomberman211]

Cái này là có vấn đề gì gì á...

Vì nếu ko sort lại thì hàm của mình sẽ chạy sai. (bạn có thể xem file để hiểu rõ hơn)

 

[befaint]

Vì nếu ko sort lại thì hàm của mình sẽ chạy sai. (bạn có thể xem file để hiểu rõ hơn)

Không phải ý đó. Dữ liệu ở sheet1 là những thứ người ta nhập vào theo thời gian thì không can thiệp vào đó...

 

[acthụavert]

Bạn có thể tham khảo thêm bài này!
http://www.giaiphapexcel.com/diendan/threads/xin-giúp-về-hàm-vlookup.124500/

Cảm ơn bác nhé, để em nghiên cứu xem sao ạ!

 

[acthụavert]

View attachment 175011
Thực ra với yêu cầu của bạn dùng VBA hoặc Pivot table sẽ tiện hơn rất nhiều.
Cơ mà mình cũng làm lại file cho bạn chỉ sử dụng Formula (có thêm 1 cột phụ đầu mỗi sheet), yêu cầu là bạn phải Sort lại bảng trong sheet 1 như hình.

Cảm ơn bác
Vấn đề này em cũng nghiên cứu rồi. Vì việc nhập liệu là liên tục, thay đổi, và chèn vào dưới, cũng như dữ liệu nhập vào không chỉ link qua mỗi cái bảng em đang vướng, mà còn link rất nhiều bảng nữa (File em khoảng 12 sheet link nhau, có những sheet 2000 rows, toàn hàm với hàm, nặng cả 10MB, chỉnh manual nhiều khi nó chạy mất cả 15 phút á) nên nếu short kiểu này hơi khó làm và thay đổi nhiều quá.
Dù sao ý của bác cũng rất hay, có lẽ tìm cách đặt điều kiện thứ 2 cho cái lô thửa để dùng sumproduct bốc 2 điều kiện chung thì sẽ ra ạ ^^
Cảm ơn again!

 

[bomberman211]

Cảm ơn bác
Vấn đề này em cũng nghiên cứu rồi. Vì việc nhập liệu là liên tục, thay đổi, và chèn vào dưới, cũng như dữ liệu nhập vào không chỉ link qua mỗi cái bảng em đang vướng, mà còn link rất nhiều bảng nữa (File em khoảng 12 sheet link nhau, có những sheet 2000 rows, toàn hàm với hàm, nặng cả 10MB, chỉnh manual nhiều khi nó chạy mất cả 15 phút á) nên nếu short kiểu này hơi khó làm và thay đổi nhiều quá.
Dù sao ý của bác cũng rất hay, có lẽ tìm cách đặt điều kiện thứ 2 cho cái lô thửa để dùng sumproduct bốc 2 điều kiện chung thì sẽ ra ạ ^^
Cảm ơn again!

Nếu tìm ra cách liệt kê chỉ dùng hàm thì share cho mình mình với nhé, mình nghĩ các hàm dạng mảng và OFFSET có thể làm được mà chưa nghĩ ra. Mà sao bạn không dùng pivot table? Mỗi lần nhập liệu xong chỉ cần Refresh 1 cái là xong.

 

[acthụavert]

Nếu tìm ra cách liệt kê chỉ dùng hàm thì share cho mình mình với nhé, mình nghĩ các hàm dạng mảng và OFFSET có thể làm được mà chưa nghĩ ra. Mà sao bạn không dùng pivot table? Mỗi lần nhập liệu xong chỉ cần Refresh 1 cái là xong.

Pivot dùng rồi bác và dùng nhiều quá nên không muốn dùng thêm á.
Có gì nếu ngâm cứu ra em sẽ share bác

 

[Hoang2013]

Xài thử hàm mảng người dùng xem sao!

 

[be_09]

Pivot dùng rồi bác và dùng nhiều quá nên không muốn dùng thêm á.
Có gì nếu ngâm cứu ra em sẽ share bác

Bạn nêu là đã dùng Pivot và dùng nhiều mà chỉ biết áp dụng chứ chưa biết ứng biến nó vào công việc nên mới lên đây hỏi, Với dữ liệu của bạn thì dùng Pivot là có kết quả ngay chứ có đắn đo gì đâu, còn cách làm thế nào thì bạn nên xem xét lại cấu trúc dữ liệu, rồi tính tiếp.

 

[acthụavert]

Xài thử hàm mảng người dùng xem sao!

Bác ơi,
File bác làm lại hay ghê. Em thấy cũng hay nhưng mà không biết viết VBA. Đã từng try hard về vụ VBA này lắm rồi nhưng em không biết viết mã. Chỉ lòe lòe mấy cái macro thao tác thôi !
Giờ phải làm như nào nhỉ. Bác hướng dẫn kỹ thêm tí được không? Hàm mảng là như nào? Mã code VBA em mở lên đọc rồi mà chả hiểu lắm, với lại ô lô thửa bác dùng combobox hay box, hay data validation vậy?
Hướng dẫn thêm em tí để em lần mò trong bóng đêm phát bác
Cảm ơn again!

 

[Hoang2013]

Bác ơi; Giờ phải làm như nào nhỉ. Bác hướng dẫn kỹ thêm tí được không?
(2) Hàm mảng là như nào?
(3) Mã code VBA em mở lên đọc rồi mà chả hiểu lắm,
(1) với lại ô lô thửa bác dùng combobox hay box, hay data validation vậy?
Hướng dẫn thêm em tí để em lần mò trong bóng đêm phát bác
Cảm ơn again!

(1) Bạn tự tìm hiểu ngay tại ô đó đi;

Đó là ô Validation mà & phải trong tầm tay của bạn chứ.

(2) Hàm mảng nên phải kết thúc bỡi tổ hợp 3 phím như hàm mảng trong Excel;

(3) VBA là ngôn ngữ, mà đã là ngôn ngữ thì gồm các khâu: Nghe; Đọc; Viết; & dịch

"Đọc rồi mà chả hiểu lắm" thì nhờ dịch những cái chưa hiểu thôi.

 

[acthụavert]

(1) Bạn tự tìm hiểu ngay tại ô đó đi;

Đó là ô Validation mà & phải trong tầm tay của bạn chứ.

(2) Hàm mảng nên phải kết thúc bỡi tổ hợp 3 phím như hàm mảng trong Excel;

(3) VBA là ngôn ngữ, mà đã là ngôn ngữ thì gồm các khâu: Nghe; Đọc; Viết; & dịch

"Đọc rồi mà chả hiểu lắm" thì nhờ dịch những cái chưa hiểu thôi.

OK cảm ơn bác nhiều nhé, để em tìm hiểu thêm. Tay ngang nên không được thông suốt lắm, hơi cực bác ạ. Có gì non kém mong bác thông cảm cho :H

 

[acthụavert]

(1) Bạn tự tìm hiểu ngay tại ô đó đi;

Đó là ô Validation mà & phải trong tầm tay của bạn chứ.

(2) Hàm mảng nên phải kết thúc bỡi tổ hợp 3 phím như hàm mảng trong Excel;

(3) VBA là ngôn ngữ, mà đã là ngôn ngữ thì gồm các khâu: Nghe; Đọc; Viết; & dịch

"Đọc rồi mà chả hiểu lắm" thì nhờ dịch những cái chưa hiểu thôi.

Tình hình là khó bác ạ!
Em xin phép làm phiền bác tí. Bây giờ, em muốn học vẹt một xíu. Bác có thể vào teamview làm giúp em các bước để được cái file như bác gửi cho em ấy, hoặc không bác quay màn hình của bác lại quá trình làm ra được cái file đó được không!
Trình có hạn, thời gian có hạn, giờ chỉ mong học vẹt được xem có áp dụng được miếng nào không.
Bác có hảo tâm thì giúp đỡ em tí!

 

[Hoang2013]

Bạn & mình cùng thực hiện các bước sau:
B 01 (Người thực hiện: Bạn): Tạo danh sách cho ô Validation tại [B3] của trang tính thể hiện số liệu báo cáo
B 02: (Người thực hiện: Mình) Diễn dịch hàm người dùng sang ngôn ngữ tiếng Việt:

Const Rws As Long = 999
Function Thua(Lo)
 Dim Arr()
 Dim J As Long, W As Long, Col As Long, Cot As Long
 ReDim dArr(1 To 99, 1 To 4) As String
1 Arr() = Sheet1.[A2].Resize(Rws, 6).Value
 For J = 1 To UBound(Arr())
3    If Arr(J, 1) = Lo Then
        W = W + 1
5        dArr(W, 1) = Arr(J, 2): dArr(W, 2) = Arr(J, 3)
        dArr(W, 3) = Arr(J, 5): dArr(W, 4) = Arr(J, 6)
7    End If
 Next J
9 Thua = dArr()
End Function

Trước khi viết hàm ta khai báo 1 hằng kiểu số (hiện đang có trị là 999)
Hằng này biểu hiện là số dòng tối đa hiện hữu tại CSDL

3 dòng lệnh trước dòng có đánh số 1: Khai báo các biến cần xài trong hàm;
Trong đó có biến mảng Arr() dùng để chứa cơ sở dữ liệu
Biến mảng dArr() - để chứa dữ liệu thỏa điều kiện khảo sát
Các biến (chứa) số dạng Long;
D1: Lấy vùng dữ liệu ở trang tính 1 gồm số dòng bằng với trị ấn định trong Const & số cột là 6
D2: Thiết lập vòng lặp theo trị J từ 1 cho đến dòng cuối của mảng chứa dữ liệu;
Vòng lặp kết thúc tại D8
D3: (Trong khi duyệt vòng lặp) điều kiện nếu trị chứa trong cột 1 của mảng bằng với trị ta đưa vô hàm để tìm thì thực hiện các câu lệnh cho tới dòng 6;
D4: Tăng biến đến W lên 1 đơn vị; (Biến này làm chỉ số dòng để lưu những giá trị thỏa điều kiện)
D5: (Gồm 2 mệnh đề)
M1: Lấy trị của cột 2 trong tại dòng đang khảo sát mảng Arr() gán vô cột 1 của dArr() (thuộc dòng W)
M2: Tương tự M1 theo cột tương ứng;
D6: Tương tự D5 với những cột tương ứng
(D78) Đã đề cập
D9: Trị trả về của hàm là mãng dữ liệu thỏa điều kiện (đã ghi trong dArr())

Lưu í trong khi xài hàm:
Theo thời gian, dữ liệu sẽ tăng lên (số dòng tăng) thì hằng số Rws ta tăng lên 1 số thích ứng

 

[acthụavert]

Bạn & mình cùng thực hiện các bước sau:
B 01 (Người thực hiện: Bạn): Tạo danh sách cho ô Validation tại [B3] của trang tính thể hiện số liệu báo cáo
B 02: (Người thực hiện: Mình) Diễn dịch hàm người dùng sang ngôn ngữ tiếng Việt:

Const Rws As Long = 999
Function Thua(Lo)
 Dim Arr()
 Dim J As Long, W As Long, Col As Long, Cot As Long
 ReDim dArr(1 To 99, 1 To 4) As String
1 Arr() = Sheet1.[A2].Resize(Rws, 6).Value
 For J = 1 To UBound(Arr())
3    If Arr(J, 1) = Lo Then
        W = W + 1
5        dArr(W, 1) = Arr(J, 2): dArr(W, 2) = Arr(J, 3)
        dArr(W, 3) = Arr(J, 5): dArr(W, 4) = Arr(J, 6)
7    End If
 Next J
9 Thua = dArr()
End Function

Trước khi viết hàm ta khai báo 1 hằng kiểu số (hiện đang có trị là 999)
Hằng này biểu hiện là số dòng tối đa hiện hữu tại CSDL

3 dòng lệnh trước dòng có đánh số 1: Khai báo các biến cần xài trong hàm;
Trong đó có biến mảng Arr() dùng để chứa cơ sở dữ liệu
Biến mảng dArr() - để chứa dữ liệu thỏa điều kiện khảo sát
Các biến (chứa) số dạng Long;
D1: Lấy vùng dữ liệu ở trang tính 1 gồm số dòng bằng với trị ấn định trong Const & số cột là 6
D2: Thiết lập vòng lặp theo trị J từ 1 cho đến dòng cuối của mảng chứa dữ liệu;
Vòng lặp kết thúc tại D8
D3: (Trong khi duyệt vòng lặp) điều kiện nếu trị chứa trong cột 1 của mảng bằng với trị ta đưa vô hàm để tìm thì thực hiện các câu lệnh cho tới dòng 6;
D4: Tăng biến đến W lên 1 đơn vị; (Biến này làm chỉ số dòng để lưu những giá trị thỏa điều kiện)
D5: (Gồm 2 mệnh đề)
M1: Lấy trị của cột 2 trong tại dòng đang khảo sát mảng Arr() gán vô cột 1 của dArr() (thuộc dòng W)
M2: Tương tự M1 theo cột tương ứng;
D6: Tương tự D5 với những cột tương ứng
(D78) Đã đề cập
D9: Trị trả về của hàm là mãng dữ liệu thỏa điều kiện (đã ghi trong dArr())

Lưu í trong khi xài hàm:
Theo thời gian, dữ liệu sẽ tăng lên (số dòng tăng) thì hằng số Rws ta tăng lên 1 số thích ứng

Quá là cảm ơn bác
Tuy không rành lắm nhưng qua các bước bác hướng dẫn và giải thích, em đã mần được rồi
Hiện tại file đã chạy ngon ơ với cách làm của bác, biết thêm một ít, vui thêm một tí!
Cảm ơn so much!