Tinh tong cac o khong lien ke

[Phan Thế Hiệp]

Dạ, em gửi bác lời giải của em ạ, em cũng mới biết Excel nên chưa thành thạo được nhiều, học hỏi chủ yếu là lên mạng xem các bài anh em giải để bắt chước nên không hiểu sâu và không hiểu cơ bản ạ, có gì sai sót mong bác giúp đỡ và chỉ giáo ạ.

Lọc trùng 3 vùng

P4=INDIRECT(TEXT(AGGREGATE(15,6,ROW($B$4:$M$30)*10^3+COLUMN($B$4:$M$30)/($B$4:$M$30<>"")/(COUNTIF($P$33,$B$4:$M$30)=0),ROW($A$1)),"r000c000"),0)

Nếu sắp xếp

Q4=INDIRECT(TEXT(MOD(AGGREGATE(15,6,(COUNTIF($B$4:$M$30,"<="&$B$4:$M$30))*10^8+ROW($B$4:$M$30)*10^3+COLUMN($B$4:$M$30)/($B$4:$M$30<>"")/(COUNTIF($Q$3:Q3,$B$4:$M$30)=0),ROW($A$1)),10^8),"R000C000"),0)

Em có gửi file đính kèm, rất mong bác chỉ bảo và góp ý giúp em ạ. Em chân thành cảm ơn bác rất nhiều ạ.

Giỏi! Có chí cầu tiến.

Nhưng do chủ đề của thớt này là lấy giá trị từng ô một, nên ở đây tôi cũng có ý: chỉ lấy dữ liệu trong 3 vùng tách biệt, không lấy dữ liệu của những vùng khác. Các cột tôi để trống thực tế sẽ có dữ liệu, như vậy với công thức bạn tạo trên sẽ lấy ra kết quả luôn, vậy thì không phải. Xem file kèm.

Thân

 

[longtay1111]

Giỏi! Có chí cầu tiến.

Nhưng do chủ đề của thớt này là lấy giá trị từng ô một, nên ở đây tôi cũng có ý: chỉ lấy dữ liệu trong 3 vùng tách biệt, không lấy dữ liệu của những vùng khác. Các cột tôi để trống thực tế sẽ có dữ liệu, như vậy với công thức bạn tạo trên sẽ lấy ra kết quả luôn, vậy thì không phải. Xem file kèm.

Thân

Dạ em rất cảm ơn sự góp ý của bác, em xin phép bác gửi lời giải ạ, có gì bác góp ý bổ sung cho em với ạ.

Em chân thành cảm ơn bác rất nhiều.

P4=INDIRECT(TEXT(MIN(IF((COUNTIF($P$33,$B$4:$M$30)=0)*($B$4:$M$30<>"")*((COLUMN($B$4:$M$30)=2)+(COLUMN($B$4:$M$30)=9)+(COLUMN($B$4:$M$30)=13)),ROW($B$4:$M$30)*10^3+COLUMN($B$4:$M$30),"")),"r0c000"),0)

Bài đã được tự động gộp: Thứ ba lúc 09:02

Dạ em rất cảm ơn sự góp ý của bác, em xin phép bác gửi lời giải ạ, có gì bác góp ý bổ sung cho em với ạ.

Em chân thành cảm ơn bác rất nhiều.

P4=INDIRECT(TEXT(MIN(IF((COUNTIF($P$33,$B$4:$M$30)=0)*($B$4:$M$30<>"")*((COLUMN($B$4:$M$30)=2)+(COLUMN($B$4:$M$30)=9)+(COLUMN($B$4:$M$30)=13)),ROW($B$4:$M$30)*10^3+COLUMN($B$4:$M$30),"")),"r0c000"),0)

Em rất xin lỗi bác Hiệp, ko hiểu sao công thức của em khi dán lại có hình mặt người, em ko có ý gì đâu ạ, em rất xin lỗi và mong bác thông cảm ạ.

Em cũng ko biết xóa bài đó như thế nào ạ.

Em xin gửi lại

P4=INDIRECT(TEXT(MIN(IF((COUNTIF($P$33,$B$4:$M$30)=0)*($B$4:$M$30<>"")*((COLUMN($B$4:$M$30)=2)+(COLUMN($B$4:$M$30)=9)+(COLUMN($B$4:$M$30)=13)),ROW($B$4:$M$30)*10^3+COLUMN($B$4:$M$30),"")),"r0c000"),0)

 

[LmoovoenX]

Dạ em rất cảm ơn sự góp ý của bác, em xin phép bác gửi lời giải ạ, có gì bác góp ý bổ sung cho em với ạ.

Em chân thành cảm ơn bác rất nhiều.

P4=INDIRECT(TEXT(MIN(IF((COUNTIF($P$33,$B$4:$M$30)=0)*($B$4:$M$30<>"")*((COLUMN($B$4:$M$30)=2)+(COLUMN($B$4:$M$30)=9)+(COLUMN($B$4:$M$30)=13)),ROW($B$4:$M$30)*10^3+COLUMN($B$4:$M$30),"")),"r0c000"),0)

Bài đã được tự động gộp: Thứ ba lúc 09:02

Em rất xin lỗi bác Hiệp, ko hiểu sao công thức của em khi dán lại có hình mặt người, em ko có ý gì đâu ạ, em rất xin lỗi và mong bác thông cảm ạ.

Em cũng ko biết xóa bài đó như thế nào ạ.

Em xin gửi lại

P4=INDIRECT(TEXT(MIN(IF((COUNTIF($P$33,$B$4:$M$30)=0)*($B$4:$M$30<>"")*((COLUMN($B$4:$M$30)=2)+(COLUMN($B$4:$M$30)=9)+(COLUMN($B$4:$M$30)=13)),ROW($B$4:$M$30)*10^3+COLUMN($B$4:$M$30),"")),"r0c000"),0)

Bạn để công thức trong code

:P

 

[longtay1111]

Bạn để công thức trong code

:P

Dạ, em cảm ơn bác nhiều ạ

 

[PHONGGANHHAO]

Do bên này cty cho nghỉ 'đại lễ', nên anh quỡn quá "thừa giấy vẽ voi":

=(N3>0)*N3+(T3>0)*T3+(X3>0)*X3
=SUM(INDEX(N3:X3*(N3:X3>0),{1,7,11}))
=SUM(--TEXT(INDEX(N3:X3,{1,7,11}),"[<0]\0"))
=SUMIFS(N3:X3,$N$1:$X$1,"phai tt*",N3:X3,">0")
=SUM(--TEXT(CHOOSE({1,2,3},T3,N3,X3),"[<0]\0"))

EM yếu excel nên chưa hiểu ý của dòng công thức này, anh giải thích giúp em với, em cảm ơn

=(N3>0)*N3+(T3>0)*T3+(X3>0)*X3

 

[huuthang_bd]

Dạ em rất cảm ơn sự góp ý của bác, em xin phép bác gửi lời giải ạ, có gì bác góp ý bổ sung cho em với ạ.

Em chân thành cảm ơn bác rất nhiều.

P4=INDIRECT(TEXT(MIN(IF((COUNTIF($P$33,$B$4:$M$30)=0)*($B$4:$M$30<>"")*((COLUMN($B$4:$M$30)=2)+(COLUMN($B$4:$M$30)=9)+(COLUMN($B$4:$M$30)=13)),ROW($B$4:$M$30)*10^3+COLUMN($B$4:$M$30),"")),"r0c000"),0)

Bài đã được tự động gộp: Thứ ba lúc 09:02

Em rất xin lỗi bác Hiệp, ko hiểu sao công thức của em khi dán lại có hình mặt người, em ko có ý gì đâu ạ, em rất xin lỗi và mong bác thông cảm ạ.

Em cũng ko biết xóa bài đó như thế nào ạ.

Em xin gửi lại

P4=INDIRECT(TEXT(MIN(IF((COUNTIF($P$33,$B$4:$M$30)=0)*($B$4:$M$30<>"")*((COLUMN($B$4:$M$30)=2)+(COLUMN($B$4:$M$30)=9)+(COLUMN($B$4:$M$30)=13)),ROW($B$4:$M$30)*10^3+COLUMN($B$4:$M$30),"")),"r0c000"),0)

1. Đang làm mảng sao lại có đoạn (...=2)+(...=9)+(...=13) => Có thể thay bằng ISNUMBER(MATCH(COLUMN($B$3:$M$3),{2,9,13}.
2. Yêu cầu lấy kết quả lần lượt theo từng cột nhưng công thức lấy theo hàng => Đổi lại nhân chỉ số cột cho 1000 và xử lý bằng các hàm REPT và MID.

 

[Phan Thế Hiệp]

Dạ em rất cảm ơn sự góp ý của bác, em xin phép bác gửi lời giải ạ, có gì bác góp ý bổ sung cho em với ạ.

Em chân thành cảm ơn bác rất nhiều.

P4=INDIRECT(TEXT(MIN(IF((COUNTIF($P$33,$B$4:$M$30)=0)*($B$4:$M$30<>"")*((COLUMN($B$4:$M$30)=2)+(COLUMN($B$4:$M$30)=9)+(COLUMN($B$4:$M$30)=13)),ROW($B$4:$M$30)*10^3+COLUMN($B$4:$M$30),"")),"r0c000"),0)

Bài đã được tự động gộp: Thứ ba lúc 09:02

Em rất xin lỗi bác Hiệp, ko hiểu sao công thức của em khi dán lại có hình mặt người, em ko có ý gì đâu ạ, em rất xin lỗi và mong bác thông cảm ạ.

Em cũng ko biết xóa bài đó như thế nào ạ.

Em xin gửi lại

P4=INDIRECT(TEXT(MIN(IF((COUNTIF($P$33,$B$4:$M$30)=0)*($B$4:$M$30<>"")*((COLUMN($B$4:$M$30)=2)+(COLUMN($B$4:$M$30)=9)+(COLUMN($B$4:$M$30)=13)),ROW($B$4:$M$30)*10^3+COLUMN($B$4:$M$30),"")),"r0c000"),0)

Tôi thích bạn đã ra sức tìm học, và làm được đến vậy. Cố gắng nha, còn nhiều điều thú vị lắm!

Bạn nên thực hiện như anh @huuthang_bd nói trên, cũng giống như cột kết quả mà tôi đã thể hiện trong file tôi gửi.

Cố lên.

________________________________________
@PHONGGANHHAO
Không nên tự ti, cố gắng học sẽ có kết quả tốt.

Mấy cái hàm đó là anh em hay chơi trên đây thôi! không "ghê gớm" gì đâu: Ý của nó là đem ô mà bạn muốn lấy trị xem có phải là số >0 hay không, nếu đúng nó sẽ trả ra trị TRUE, nếu sai trả về trị FALSE. Ép True/False cộng trừ nhân chia với 1 số bất kỳ, lúc đó True sẽ bị 'ép' thành trị 1, False thành trị 0.

Thật ra, bạn có thể làm đơn giản như bài #2 hướng dẫn:
=If(N3>0, N3,0) + If(T3>0, T3,0) + If(X3>0, X3,0)

Hoặc ý bài #8:
=Max(N3,0)+Max(T3,0)+Max(X3,0)

Mấy công thức còn lại như tôi đã nói "thừa giấy vẽ voi" thôi, đó là anh em tôi trêu đùa nhau, cũng là gây sự vui thích, tìm lý thú trong việc học hàm thôi!

Chúc bạn học vui

 

[longtay1111]

1. Đang làm mảng sao lại có đoạn (...=2)+(...=9)+(...=13) => Có thể thay bằng ISNUMBER(MATCH(COLUMN($B$3:$M$3),{2,9,13}.
2. Yêu cầu lấy kết quả lần lượt theo từng cột nhưng công thức lấy theo hàng => Đổi lại nhân chỉ số cột cho 1000 và xử lý bằng các hàm REPT và MID.

Dạ em cảm ơn bác về sự góp ý ạ, em làm như vậy là do em đang hiểu ý bác Hiệp là vùng trống( các cột trống sau này sẽ bổ sung dữ liệu sau ), khi lọc dữ liệu như kết quả cột O thì khi bổ sung dữ liệu vào các cột trống đó vẫn không làm thay đổi kết quả của cột O.

Em có bôi vàng phần cột trống và có thử bổ sung thêm 1 vài dữ liệu nhỏ ở cột trống này nhưng kết quả vẫn không thay đổi( kết quả vẫn như cột O mà bác Hiệp đã yêu cầu ).

Em xin gửi công thức sau khi đã chỉnh sửa( lấy dữ liệu theo cột không phải theo hàng em đã làm như lời góp ý của bác Huuthang ạ ).

Một lần nữa em chân thành cảm ơn sự góp ý nhiệt tình của bác Huuthang, em chúc bác 1 ngày mới nhiều niềm vui ạ

P4=INDIRECT(MID(REPT(TEXT(MIN(IF((COUNTIF(P$33,B$4:M$30)=0)*(B$4:M$30<>"")*($B$3:$M$3<>""),ROW(B$4:M$30)+COLUMN(B$4:M$30)*100,"")),"c00r00"),2),4,6),) ​

 

[Phan Thế Hiệp]

Dạ em cảm ơn bác về sự góp ý ạ, em làm như vậy là do em đang hiểu ý bác Hiệp là vùng trống( các cột trống sau này sẽ bổ sung dữ liệu sau ), khi lọc dữ liệu như kết quả cột O thì khi bổ sung dữ liệu vào các cột trống đó vẫn không làm thay đổi kết quả của cột O.

Em có bôi vàng phần cột trống và có thử bổ sung thêm 1 vài dữ liệu nhỏ ở cột trống này nhưng kết quả vẫn không thay đổi( kết quả vẫn như cột O mà bác Hiệp đã yêu cầu ).

Em xin gửi công thức sau khi đã chỉnh sửa( lấy dữ liệu theo cột không phải theo hàng em đã làm như lời góp ý của bác Huuthang ạ ).

Một lần nữa em chân thành cảm ơn sự góp ý nhiệt tình của bác Huuthang, em chúc bác 1 ngày mới nhiều niềm vui ạ

P4=INDIRECT(MID(REPT(TEXT(MIN(IF((COUNTIF(P$33,B$4:M$30)=0)*(B$4:M$30<>"")*($B$3:$M$3<>""),ROW(B$4:M$30)+COLUMN(B$4:M$30)*100,"")),"c00r00"),2),4,6),) ​

Chưa ổn lắm: rủi ($B$3:$M$3<>"") đều là dòng có tên đề mục hết thì sao?

Tiếp tục...

 

[PHONGGANHHAO]

Tôi thích bạn đã ra sức tìm học, và làm được đến vậy. Cố gắng nha, còn nhiều điều thú vị lắm!

Bạn nên thực hiện như anh @huuthang_bd nói trên, cũng giống như cột kết quả mà tôi đã thể hiện trong file tôi gửi.

Cố lên.

________________________________________
@PHONGGANHHAO
Không nên tự ti, cố gắng học sẽ có kết quả tốt.

Mấy cái hàm đó là anh em hay chơi trên đây thôi! không "ghê gớm" gì đâu: Ý của nó là đem ô mà bạn muốn lấy trị xem có phải là số >0 hay không, nếu đúng nó sẽ trả ra trị TRUE, nếu sai trả về trị FALSE. Ép True/False cộng trừ nhân chia với 1 số bất kỳ, lúc đó True sẽ bị 'ép' thành trị 1, False thành trị 0.

Thật ra, bạn có thể làm đơn giản như bài #2 hướng dẫn:
=If(N3>0, N3,0) + If(T3>0, T3,0) + If(X3>0, X3,0)

Hoặc ý bài #8:
=Max(N3,0)+Max(T3,0)+Max(X3,0)

Mấy công thức còn lại như tôi đã nói "thừa giấy vẽ voi" thôi, đó là anh em tôi trêu đùa nhau, cũng là gây sự vui thích, tìm lý thú trong việc học hàm thôi!

Chúc bạn học vui

Dạ em cảm ơn tất cả anh/chị đã chỉ em ạ

 

[longtay1111]

Chưa ổn lắm: rủi ($B$3:$M$3<>"") đều là dòng có tên đề mục hết thì sao?

Tiếp tục...

Dạ, em gửi bác công thức này ạ, em chưa nghĩ được phương án tối ưu hơn ạ, có gì bác góp ý giúp em với

P4=INDIRECT(MID(REPT(TEXT(MIN(IF((COUNTIF(P$33,B$4:M$30)=0)*(B$4:M$30<>"")*($B$3:$M$3<>"")*{1,0,0,0,1,0,0,0,0,0,0,1},ROW(B$4:M$30)+COLUMN(B$4:M$30)*100,"")),"c00r00"),2),4,6),)

Em chân thành cảm ơn bác rất nhiều ạ.

 

[LmoovoenX]

Chưa ổn lắm: rủi ($B$3:$M$3<>"") đều là dòng có tên đề mục hết thì sao?

Tiếp tục...

Em góp vui :

T4=INDIRECT(MID(REPT(TEXT(MIN(IF(COUNTIF(T3:T$3;CHOOSE({1\2\3};$B$4:$B$30;$F$4:$F$30;$M$4:$M$30));"";IF(CHOOSE({1\2\3};$B$4:$B$30;$F$4:$F$30;$M$4:$M$30)=0;"";{200\600\1300}+ROW($B$4:$B$30))));"c00r00");2);4;6);0)

 

[Phan Thế Hiệp]

P4=INDIRECT(MID(REPT(TEXT(MIN(IF((COUNTIF(P$33,B$4:M$30)=0)*(B$4:M$30<>"")*($B$3:$M$3<>"")*{1,0,0,0,1,0,0,0,0,0,0,1},ROW(B$4:M$30)+COLUMN(B$4:M$30)*100,"")),"c00r00"),2),4,6),)

Khi gõ trực tiếp vào bài trả lời, bạn muốn tránh ký hiệu 'biểu cảm' thì tách bằng 1 khoảng trắng sau dấu ": ", ví dụ: "P$3: P3" sẽ ổn.

Dùng ($B$3: $M$3<>"")*{1,0,0,0,1,0,0,0,0,0,0,1} cũng là 1 cách xử lý. Hoặc bạn cũng có thể dùng Match() như anh @huuthang_bd đã chỉ.

CHOOSE({1\2\3};$B$4:$B$30;$F$4:$F$30;$M$4:$M$30))

Ngoài Choose({1,2,3},...) gộp được 3 vùng tách biệt thành 1 mảng thống nhất 2 chiều, vậy còn hàm nào có thể làm được chuyện này không!?

 

[VetMini]

Em góp vui :

T4=INDIRECT(MID(REPT(TEXT(MIN(IF(COUNTIF(T3:T$3;CHOOSE({1\2\3};$B$4:$B$30;$F$4:$F$30;$M$4:$M$30));"";IF(CHOOSE({1\2\3};$B$4:$B$30;$F$4:$F$30;$M$4:$M$30)=0;"";{200\600\1300}+ROW($B$4:$B$30))));"c00r00");2);4;6);0)

1. Hàm Indirect là hàm cần tránh nhất. Ở đây lại có một đống hàm mảng. Một lần có gì trên sheet là nó tính lại cho chết tươi. Nếu có thể tìm cách khác thì nên tìm.
2. Tối thiểu cũng phải bỏ mấy cái hàm tính hằng vào names cho nó tính hiệu quả hơn:
name1 = CHOOSE({1\2\3};$B$4:$B$30;$F$4:$F$30;$M$4:$M$30)
name2 = {200\600\1300}+ROW($B$4:$B$30)
(ROW($B$4:$B$30) luôn luôn là 4, nhưng bạn muốn để đó với mục đích đồng nhất công thức cũng là tốt)