Giúp công thức Lấy kết quả theo điều kiện

[dvt408b]

Kiến thức excel mình rất kém mà lại đang cần gấp. Mong các bạn giúp dùm bài toán theo file đính kèm:
Nội dung sơ lược thế này:
1- Mình có dữ liệu số ở Cột E và Cột M
2- Cột G là cột so sánh số liệu của Cột M so với Cột E. Nếu giống nhau thì tại dòng của đó sẽ hiện số liệu giống nhau.
3- Vì dữ liệu Cột E và Cột M rất nhiều nhưng giống nhau thì ít. Vì vậy dữ liệu xuất hiện tại Cột G sẽ gián đoạn (không liên tục). Mình muốn làm 1 khu vực riêng biệt để tách kết quả các số liệu giống nhau này ra cho dễ nhìn (tạm gọi là KHU KẾT QUẢ), các số giống nhau này sẽ nằm liên tục không gián đoạn như bên Cột G. Nhưng khi cập nhật thêm dữ liệu cho Cột E và Cột M, nếu có số giống nhau nữa thì bên KHU KẾT QUẢ cũng sẽ tự động cập nhật số giống nhau này.

Mình có file kèm theo. Trong file này thì mình đã giải quyết được mục số 2, còn mục số 3 thì mình pó tay. Mình rất yếu về excel, vì vậy nếu có thể nên dùng công thức, không dùng lập trình. Mong các pro chỉ giúp.
Xin cảm ơn rất nhiều.

 

[boyxin]

Kiến thức excel mình rất kém mà lại đang cần gấp. Mong các bạn giúp dùm bài toán theo file đính kèm:
Nội dung sơ lược thế này:
1- Mình có dữ liệu số ở Cột E và Cột M
2- Cột G là cột so sánh số liệu của Cột M so với Cột E. Nếu giống nhau thì tại dòng của đó sẽ hiện số liệu giống nhau.
3- Vì dữ liệu Cột E và Cột M rất nhiều nhưng giống nhau thì ít. Vì vậy dữ liệu xuất hiện tại Cột G sẽ gián đoạn (không liên tục). Mình muốn làm 1 khu vực riêng biệt để tách kết quả các số liệu giống nhau này ra cho dễ nhìn (tạm gọi là KHU KẾT QUẢ), các số giống nhau này sẽ nằm liên tục không gián đoạn như bên Cột G. Nhưng khi cập nhật thêm dữ liệu cho Cột E và Cột M, nếu có số giống nhau nữa thì bên KHU KẾT QUẢ cũng sẽ tự động cập nhật số giống nhau này.

Mình có file kèm theo. Trong file này thì mình đã giải quyết được mục số 2, còn mục số 3 thì mình pó tay. Mình rất yếu về excel, vì vậy nếu có thể nên dùng công thức, không dùng lập trình. Mong các pro chỉ giúp.
Xin cảm ơn rất nhiều.

Bạn kiểm tra lại kết quả nhé

 

[concogia]

Kiến thức excel mình rất kém mà lại đang cần gấp. Mong các bạn giúp dùm bài toán theo file đính kèm:
Nội dung sơ lược thế này:
1- Mình có dữ liệu số ở Cột E và Cột M
2- Cột G là cột so sánh số liệu của Cột M so với Cột E. Nếu giống nhau thì tại dòng của đó sẽ hiện số liệu giống nhau.
3- Vì dữ liệu Cột E và Cột M rất nhiều nhưng giống nhau thì ít. Vì vậy dữ liệu xuất hiện tại Cột G sẽ gián đoạn (không liên tục). Mình muốn làm 1 khu vực riêng biệt để tách kết quả các số liệu giống nhau này ra cho dễ nhìn (tạm gọi là KHU KẾT QUẢ), các số giống nhau này sẽ nằm liên tục không gián đoạn như bên Cột G. Nhưng khi cập nhật thêm dữ liệu cho Cột E và Cột M, nếu có số giống nhau nữa thì bên KHU KẾT QUẢ cũng sẽ tự động cập nhật số giống nhau này.

Mình có file kèm theo. Trong file này thì mình đã giải quyết được mục số 2, còn mục số 3 thì mình pó tay. Mình rất yếu về excel, vì vậy nếu có thể nên dùng công thức, không dùng lập trình. Mong các pro chỉ giúp.
Xin cảm ơn rất nhiều.

3- Vì dữ liệu Cột E và Cột M rất nhiều nhưng giống nhau thì ít. Vì vậy dữ liệu xuất hiện tại Cột G sẽ gián đoạn (không liên tục).
Nếu chỉ vì lý do này mà phải tạo thêm một khu vực nữa, thêm một đống công thức nữa thì sao bạn không lấy kết quả liên tục ngay ở cột G cho rồi
Cái này là ý riêng của mình có gì không đúng bỏ qua nhé
Thân

 

[dvt408b]

Cảm ơn 2 bạn nhiều. Công thức của 2 bạn rất hay. Đọc xong thấy mình chả là cái gì cả. Hic hic

to: boyxin
Cách của bạn đúng sát yêu cầu, nhưng mình còn 3 cái thắc mắc trong công thức mong bạn giải thích cho mình hiểu:
=IF(ROWS($1:1)>COUNT(boyxin),"",INDEX(Gnum,SMALL(boyxin,ROWS($1:1)

1- Giá trị GPE boyxin được định nghĩa như thế nào? nó là giá trị gì? làm thế nào để định nghĩa nó? Mình có thể hiệu chỉnh nó không?
2- Giá trị Gnum trong lệnh INDEX là cái nào? định nghĩa và hiệu chỉnh ra sao?
3- Tại sao khi mình thêm số Rương bên cột E thì bên cột R tự cập nhật. Nhưng ngược lại, khi thêm số Key bên Cột M thì cột R không cập nhật???

------------------
to: concogia
"Nếu chỉ vì lý do này mà phải tạo thêm một khu vực nữa, thêm một đống công thức nữa thì sao bạn không lấy kết quả liên tục ngay ở cột G cho rồi
Cái này là ý riêng của mình có gì không đúng bỏ qua nhé"
-------------------
Cảm ơn bạn rất nhiều, cách của bạn cũng rất hay, khi thêm bên cột nào thì nó cũng tự cập nhật,điều này giúp mình học hỏi nhiều hơn. Tuy nhiên mình muốn trích ra khu vực riêng để nó không ảnh hưởng đến khu vực origin, đồng thời tại khu vực riêng này thì khi có giá trị trùng nhau thì nó sẽ hiện luôn được Key của ai và Rương của ai tương ứng, mình muốn in ấn hoặc làm một số công thức khác nữa cũng dễ. Sẵn cho mình hỏi công thức
=IF(ROW(A1)>COUNT(cot),"",INDEX($E$4:$E$1000,SMALL(cot,ROW(A1))))
thì ý nghĩa chính của lệnh như thế nào?

Do kiến thức excel của mình hạn chế mong 2 bạn giải thích ý nghĩa chính của dòng lệnh để mình học hỏi thêm lệnh nhé.
Rất vui được giúp đỡ.

 

[boyxin]

Cảm ơn 2 bạn nhiều. Công thức của 2 bạn rất hay. Đọc xong thấy mình chả là cái gì cả. Hic hic

to: boyxin
Cách của bạn đúng sát yêu cầu, nhưng mình còn 3 cái thắc mắc trong công thức mong bạn giải thích cho mình hiểu:
=IF(ROWS($1:1)>COUNT(boyxin),"",INDEX(Gnum,SMALL(boyxin,ROWS($1:1)

1- Giá trị boyxin được định nghĩa như thế nào? nó là giá trị gì? làm thế nào để định nghĩa nó? Mình có thể hiệu chỉnh nó không?
2- Giá trị Gnum trong lệnh INDEX là cái nào? định nghĩa và hiệu chỉnh ra sao?
3- Tại sao khi mình thêm số Rương bên cột E thì bên cột R tự cập nhật. Nhưng ngược lại, khi thêm số Key bên Cột M thì cột R không cập nhật???

Do kiến thức excel của mình hạn chế mong 2 bạn giải thích ý nghĩa chính của dòng lệnh để mình học hỏi thêm lệnh nhé.
Rất vui được giúp đỡ.

Trong file mình đã sử dụng 2 name: boyxin và Gnum mà bạn đã thấy trong công thức. Muốn tìm hiểu hãy vào Insert \ Name \ Define ... và cũng sẽ hiểu thêm được cách làm của bác concogia

Bạn sửa lại công thức của Gnum (vùng dữ liệu số có trong cột G) như sau thì thêm số vào cột M hay cột E thì cột R sẽ cập nhật

Gnum=OFFSET(Daihai!$G$4,,,MAX(COUNT(Daihai!$E$4:$E$1000),COUNT(Daihai!$M$4:$M$1000)))

 

[dvt408b]

Trong file mình đã sử dụng 2 name: boyxin và Gnum mà bạn đã thấy trong công thức. Muốn tìm hiểu hãy vào Insert \ Name \ Define ... và cũng sẽ hiểu thêm được cách làm của bác concogia

Bạn sửa lại công thức của Gnum (vùng dữ liệu số có trong cột G) như sau thì thêm số vào cột M hay cột E thì cột R sẽ cập nhật

Gnum=OFFSET(Daihai!$G$4,,,MAX(COUNT(Daihai!$E$4:$E$1000),COUNT(Daihai!$M$4:$M$1000)))

Cảm ơn bạn. Sau khi mình tham khảo mấy công thức của hai bạn thì mình hiểu được chút ít và thấy rằng tình trạng mà thêm số mới vào nó không cập nhật là do vùng OFFSET của boyxin chỉ lấy được giá trị trong vùng tham khảo khoảng 25 hàng, nếu số liệu nhập vào nằm ở vị trí ngoài vùng OFSET này (nằm hàng 26 trở đi) thì dữ liệu bên cột R sẽ không cập nhật nếu Rương và Key trùng nhau.
Vì vậy nếu có thay công thức mới cho Gnum thì cũng không thay đổi vùng OFFSET. Mình thử chỉ chỉnh lại Gnum cũ bằng Gnum mới thì có vẽ hợp lý, nhờ mấy bạn kiểm xem có đúng không nhé:
+ Công thức Gnum cũ:
Gnum =OFFSET(Daihai!$G$4,,,COUNT(Daihai!$E$4:$E$1000))
+ Công thức Gnum mới:
Gnum=OFFSET(Daihai!$G$4,,,1000)

- Với trình độ yếu kém của mình thì sau một thời gian đọc qua 2 cách của boyxin và concogia để tìm hiểu, dù ra kết quả giống nhau thì mình thấy công thức của concogia tương đối đơn giản hơn của boyxin. Nhưng vẫn chưa hiểu cho lắm lý luận theo công thức của 2 bạn.

 

[concogia]

Cảm ơn bạn. Sau khi mình tham khảo mấy công thức của hai bạn thì mình hiểu được chút ít và thấy rằng tình trạng mà thêm số mới vào nó không cập nhật là do vùng OFFSET của boyxin chỉ lấy được giá trị trong vùng tham khảo khoảng 25 hàng, nếu số liệu nhập vào nằm ở vị trí ngoài vùng OFSET này (nằm hàng 26 trở đi) thì dữ liệu bên cột R sẽ không cập nhật nếu Rương và Key trùng nhau.
Vì vậy nếu có thay công thức mới cho Gnum thì cũng không thay đổi vùng OFFSET. Mình thử chỉ chỉnh lại Gnum cũ bằng Gnum mới thì có vẽ hợp lý, nhờ mấy bạn kiểm xem có đúng không nhé:
+ Công thức Gnum cũ:
Gnum =OFFSET(Daihai!$G$4,,,COUNT(Daihai!$E$4:$E$1000))
+ Công thức Gnum mới:
Gnum=OFFSET(Daihai!$G$4,,,10000,)

Nếu bạn chắc chắn rằng:
-Dữ liệu của bạn là Số
-Dữ liệu được cập nhật liên tục_ không có khoảng trống
Thì bạn dùng công thức:
Gnum =OFFSET(Daihai!$G$4,,,COUNT(Daihai!$E$4:$E$1000))
Công thức này trả về kết quả là một mảng có số dòng bằng với số dòng chứa dữ liệu trong cột E, từ E4 đến E1000
Công thức này:
Gnum=OFFSET(Daihai!$G$4,,,10000,) ==> luôn luôn cho ra kết quả là một mảng gồm 10.000 dòng tínhtừ G4 bất kể từ G4 trở xuống có bao nhiêu dòng chứa dữ liệu miễn là =< 10.000
Híc, hy vọng bạn thấy được cái khác nhau của "tụi nó"

 

[dvt408b]

Nếu bạn chắc chắn rằng:
-Dữ liệu của bạn là Số
-Dữ liệu được cập nhật liên tục_ không có khoảng trống
Thì bạn dùng công thức:
Gnum =OFFSET(Daihai!$G$4,,,COUNT(Daihai!$E$4:$E$1000))
Công thức này trả về kết quả là một mảng có số dòng bằng với số dòng chứa dữ liệu trong cột E, từ E4 đến E1000
Công thức này:
Gnum=OFFSET(Daihai!$G$4,,,10000,) ==> luôn luôn cho ra kết quả là một mảng gồm 10.000 dòng tínhtừ G4 bất kể từ G4 trở xuống có bao nhiêu dòng chứa dữ liệu miễn là =< 10.000
Híc, hy vọng bạn thấy được cái khác nhau của "tụi nó"

Thì ra là vậy. Vì dữ liệu mình luôn là số mà không phải lúc nào cũng liên tục, trong trường hợp nếu hai số trùng nhau rồi, mình sẽ lấy nó ra và xoá đi, như vậy tại vị trí đó sẽ trống. Chính vỉ vậy mà khi mình thử thêm dữ liệu số vào hàng thứ 30 thì nó chẳng ra được. Sau khi đọc giải thích của bạn, mình mới thử lại công thức của boyxin bằng cách điền số liên tục thì đúng là nó ra kết quả . Đúng là toàn cao thủ. Tuy nhiên, khi dùng cách của bạn concogia thấy cũng ra kết quả mà tương đối dễ hiểu với mình. Xin cám ơn vì đã giúp mình hiểu được thêm nhiều điều mới mẻ, thú vị (vì mình cũng ít có thời gian để đọc hết những kiến thức mà giaiphapexcel.com đưa ra). Nếu có thời gian để đọc hết nội dung trong giaiphapexcel.com mình nghĩ kiến thức mình sẽ không đến nỗi tệ thế này . Hy vọng sẽ được sự giúp đỡ của các bạn nhiều hơn.